REmove Duplicates - Keeping most recent

The recommended method of customizing your AppGini-generated application is through hooks. But sometimes you might need to add functionality not accessible through hooks. You can discuss this here.
Post Reply
jangle
Veteran Member
Posts: 89
Joined: 2020-01-18 17:41

REmove Duplicates - Keeping most recent

Post by jangle » 2020-04-20 18:47

Hello forum,

I have a table where people insert a status, total people and total people out each day.

I need to have a way to create a summary table that removes duplicates and only keeps the most recent submittal. I know I can pull out into excel, but I wanted to try and automate the is AP.

Through filters maybe????

Thanks in advance

Jim

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: REmove Duplicates - Keeping most recent

Post by pbottcher » 2020-04-20 18:55

Hi,
can you explain how your table looks like? What does "most recent" mean? Based on which information?
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

jangle
Veteran Member
Posts: 89
Joined: 2020-01-18 17:41

Re: REmove Duplicates - Keeping most recent

Post by jangle » 2020-04-20 20:03

Hi,

The table has 10 fields, one of which is organization name (fire department). One other is date submitted. We are tracking members off so each dat an organization would submit the form. Let say 10 total - 5 out. Lets say the next day when they submit it is 10 total three out (2 came back) if I sum up all from the organization I would have 20 and 8 out when really in is 210 and 3 as the most recent submittal. So I would like a way to sure, then csv download the most recent only.

Hope that makes sense...

Thanks for the help.

Jim

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: REmove Duplicates - Keeping most recent

Post by pbottcher » 2020-04-20 21:21

Hi,

not sure how you want to get the result presented, but I would create a "report" table as a copy of your input table and then replace it with a view in the database to directly reflect only the required information.

Something like

SELECT t1.*
FROM TABLENAME t1
WHERE t1.ID = (SELECT t2.ID
FROM TABLENAME t2
WHERE t2.ORGANISATION = t1.ORGANISATION
ORDER BY t2.DATE DESC
LIMIT 1)

Replace TABLENAME with your tablename, ID with your PK (if different), ORGANISATION, DATE with the respective fields of your table.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

jangle
Veteran Member
Posts: 89
Joined: 2020-01-18 17:41

Re: REmove Duplicates - Keeping most recent

Post by jangle » 2020-04-20 21:44

Thanks pböttcher

Let me try that's.... I think, if I can get it right that that would work...

Would I out that code in hooks?????

jangle
Veteran Member
Posts: 89
Joined: 2020-01-18 17:41

Re: REmove Duplicates - Keeping most recent

Post by jangle » 2020-04-21 02:54

Disregard last question. I see now to create the view in the database.

Hope it works!!

Thanks

jangle
Veteran Member
Posts: 89
Joined: 2020-01-18 17:41

Re: REmove Duplicates - Keeping most recent

Post by jangle » 2020-04-21 13:40

Does this look right, it does not produce a result???

Code: Select all

SELECT * FROM Workforce_Impact_Survey WHERE workforce_impact_survey.ID = (SELECT workforce_impact_report.ID FROM workforce_impact_report WHERE workforce_impact_report.Fire_Department = workforce_impact_survey.Fire_Department ORDER BY workforce_impact_report.`date_submitted` DESC LIMIT 1)
Thanks!!

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: REmove Duplicates - Keeping most recent

Post by pbottcher » 2020-04-21 17:53

Hi,

can you post the definition of your tables. Which is the common key between both?
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

jangle
Veteran Member
Posts: 89
Joined: 2020-01-18 17:41

Re: REmove Duplicates - Keeping most recent

Post by jangle » 2020-04-21 19:08

This is the original table

Code: Select all

CREATE TABLE `workforce_impact_survey` (
  `ID` int(10) UNSIGNED NOT NULL,
  `date_submitted` datetime DEFAULT NULL,
  `Fire_Department` int(10) UNSIGNED NOT NULL,
  `Total_operational_Workforce` int(5) NOT NULL,
  `workforce_out` int(5) NOT NULL,
  `Percentage_Out` decimal(3,0) DEFAULT NULL,
  `County` int(10) UNSIGNED DEFAULT NULL,
  `Region` int(10) UNSIGNED DEFAULT NULL,
  `Total_Positive` varchar(40) NOT NULL,
  `IAFF_Local` int(10) UNSIGNED DEFAULT NULL,
  `Address` int(10) UNSIGNED DEFAULT NULL,
  `Email` varchar(80) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
This is the copy I made that I want to make the view.

Code: Select all

CREATE TABLE `workforce_impact_report` (
  `ID` int(10) UNSIGNED NOT NULL,
  `date_submitted` datetime DEFAULT NULL,
  `Fire_Department` int(10) UNSIGNED DEFAULT NULL,
  `Address` int(10) UNSIGNED DEFAULT NULL,
  `County` int(10) UNSIGNED DEFAULT NULL,
  `Region` int(10) UNSIGNED DEFAULT NULL,
  `IAFF_Local` int(10) UNSIGNED DEFAULT NULL,
  `Total_operational_Workforce` int(5) DEFAULT NULL,
  `workforce_out` int(5) DEFAULT NULL,
  `Percentage_Out` decimal(3,0) DEFAULT NULL,
  `Total_Positive` varchar(40) DEFAULT NULL,
  `Email` varchar(80) DEFAULT NULL

Thanks again

Jim

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: REmove Duplicates - Keeping most recent

Post by pbottcher » 2020-04-21 20:17

Hi,
can you try

SELECT * FROM Workforce_Impact_Survey WHERE workforce_impact_survey.Fire_Department = (SELECT workforce_impact_report.Fire_Department FROM workforce_impact_report WHERE workforce_impact_report.Fire_Department = workforce_impact_survey.Fire_Department ORDER BY workforce_impact_report.`date_submitted` DESC LIMIT 1)
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

jangle
Veteran Member
Posts: 89
Joined: 2020-01-18 17:41

Re: REmove Duplicates - Keeping most recent

Post by jangle » 2020-04-22 01:26

I am stumped....

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0016 seconds.)

Jim

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: REmove Duplicates - Keeping most recent

Post by pbottcher » 2020-04-22 10:38

Hi,

sorry, I put a wrong information in.

Can you try

SELECT t1.* FROM workforce_impact_survey t1 WHERE t1.ID = (SELECT t2.ID FROM workforce_impact_survey t2 WHERE t2.Fire_Department = t1.Fire_Department ORDER BY t2.date_submitted DESC LIMIT 1)
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

jangle
Veteran Member
Posts: 89
Joined: 2020-01-18 17:41

Re: REmove Duplicates - Keeping most recent

Post by jangle » 2020-04-22 13:12

First: don't be sorry.... I appreciate you help very much.

Second: it seems to work. I can put the query into the "report" table and it seems to work. How do I get that saved for that report and the call it in my AP program?

Thanks

jangle
Veteran Member
Posts: 89
Joined: 2020-01-18 17:41

Re: REmove Duplicates - Keeping most recent

Post by jangle » 2020-04-22 14:20

I have the report saved in my DB. When I click on the view in phpadmin it works great..

Last piece how to I get to it from my AG program menu?

Thanks

jangle
Veteran Member
Posts: 89
Joined: 2020-01-18 17:41

Re: REmove Duplicates - Keeping most recent

Post by jangle » 2020-04-22 17:35

I have tried to add to hooks: Workforce_Impact_Report_init

SELECT * FROM `report_view`
Or
$QueryFrom = `report_view`
Or
$sql = "SELECT * FROM `report_view`";

While it works in sql (phpadmin) I cannot get to work in AppGini

Thanks

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: REmove Duplicates - Keeping most recent

Post by pbottcher » 2020-04-22 18:24

Hi,

you have the table workforce_impact_report create in AppGini.

Now you use phpmyadmin and

1, drop table workforce_impact_report
2, create view workforce_impact_report as SELECT t1.* FROM workforce_impact_survey t1 WHERE t1.ID = (SELECT t2.ID FROM workforce_impact_survey t2 WHERE t2.Fire_Department = t1.Fire_Department ORDER BY t2.date_submitted DESC LIMIT 1)

Now your "Tableview" in your app shall show the requested data
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

jangle
Veteran Member
Posts: 89
Joined: 2020-01-18 17:41

Re: REmove Duplicates - Keeping most recent

Post by jangle » 2020-04-22 20:28

WOW, I think it might be working...…..

Thank you so much, I learned a great deal! and solved a problem.

Jim

jangle
Veteran Member
Posts: 89
Joined: 2020-01-18 17:41

Re: REmove Duplicates - Keeping most recent

Post by jangle » 2020-04-22 21:02

So if I make changes and recompile will that have to be done again?

Hopefully the final question.
Thanks

Jim

jangle
Veteran Member
Posts: 89
Joined: 2020-01-18 17:41

Re: REmove Duplicates - Keeping most recent

Post by jangle » 2020-04-23 12:50

Disregard I think I figured that one out!

Thanks again!

Jim

jangle
Veteran Member
Posts: 89
Joined: 2020-01-18 17:41

Re: REmove Duplicates - Keeping most recent

Post by jangle » 2020-04-29 02:56

Hello Forum,

I now need a daily report from the view that is created with the query - SELECT t1.* FROM workforce_impact_survey t1 WHERE t1.ID = (SELECT t2.ID FROM workforce_impact_survey t2 WHERE t2.Fire_Department = t1.Fire_Department ORDER BY t2.date_submitted DESC LIMIT 1) -

I need to pull a count or records, sum of workforce, sum of members out and sum of tests. I would pull this from the current data each morning and save by date. I would also like to break down further by the region field.

I have tried by create a table in appgini but can’t really get it to do what I need.

If someone could point me in the right direction.

Thanks

Jim

jangle
Veteran Member
Posts: 89
Joined: 2020-01-18 17:41

Re: REmove Duplicates - Keeping most recent

Post by jangle » 2020-10-05 20:43

Hello pböttcher and forum,

I think you might remember the help you gave me on this. It has been working great.

Code: Select all

select `t1`.`ID` AS `ID`,`t1`.`date_submitted` AS `date_submitted`,`t1`.`Fire_Department` AS `Fire_Department`,`t1`.`Total_operational_Workforce` AS `Total_operational_Workforce`,`t1`.`workforce_out` AS `workforce_out`,`t1`.`Percentage_Out` AS `Percentage_Out`,`t1`.`County` AS `County`,`t1`.`Region` AS `Region`,`t1`.`Total_Positive` AS `Total_Positive`,`t1`.`IAFF_Local` AS `IAFF_Local`,`t1`.`Address` AS `Address`,`t1`.`Email` AS `Email` from `serp`.`workforce_impact_survey` `t1` WHERE `t1`.`ID` = (select `t2`.`ID` from `serp`.`workforce_impact_survey` `t2` where `t2`.`Fire_Department` = `t1`.`Fire_Department` order by `t2`.`date_submitted` desc limit 1)
Now I need this page to only pull from the last 30 day submissions. I got the following code to work fine on another table, but was hopoing you could tell me where to put it in the above statement.

Code: Select all

WHERE `date_submitted` BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW()
I am hoping that you have a minute to tell me.

Thanks

Jim

jangle
Veteran Member
Posts: 89
Joined: 2020-01-18 17:41

Re: REmove Duplicates - Keeping most recent

Post by jangle » 2020-10-06 20:17

Please disregard..... I got it.....

Thanks

Code: Select all

select `t1`.`ID` AS `ID`,`t1`.`date_submitted` AS `date_submitted`,`t1`.`Fire_Department` AS `Fire_Department`,`t1`.`Total_operational_Workforce` AS `Total_operational_Workforce`,`t1`.`workforce_out` AS `workforce_out`,`t1`.`Percentage_Out` AS `Percentage_Out`,`t1`.`County` AS `County`,`t1`.`Region` AS `Region`,`t1`.`Total_Positive` AS `Total_Positive`,`t1`.`IAFF_Local` AS `IAFF_Local`,`t1`.`Address` AS `Address`,`t1`.`Email` AS `Email`,`t1`.`Operational_Impacts` AS `Operational_Impacts`,`t1`.`Strategies_Used_to_Overcome` AS `Strategies_Used_to_Overcome` from `serp`.`workforce_impact_survey` `t1` where `t1`.`ID` = (select `t2`.`ID` from `serp`.`workforce_impact_survey` `t2` where `t2`.`Fire_Department` = `t1`.`Fire_Department` and `t2`.`date_submitted` between current_timestamp() - interval 30 day and current_timestamp() order by `t2`.`date_submitted` desc limit 1)

Post Reply