Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Choose maxdate value of MAUC

Hello,

I have a table from external database and I need to write a correct script. Script should be summing MAUC value in max dates.

Table is now:

STOREITEMDATE UPDATINGCOMPONENTMAUC
M20m68758704/20/2019140€ 20
M20m68758704/20/2019500€ 2
M20m68758731/03/202014025 €
M20m68758731/03/2020500€ 3
M35m68758702/12/202014017 €
M34m33333302/20/2017110170 €
M34m33333302/20/201750017 €
M34m33333308/19/2019110190 €
M34m33333308/19/2019500

23 €

I need FINAL table in which will be:

1. only max date updating

2. sum of MAUC according to different component

STOREITEMDATE UPDATING (MAX)MAUC
M20m68758731/03/202028 €
M35m68758702/12/202017 €
M34m33333308/19/2019€ 213

 

Could you help me, how can I do this ?

Thank you.

Labels (3)
1 Solution

Accepted Solutions
Highlighted

HI, if you do an inner join with

Inner Join (DataTable)
LOAD 
  STORE,
  ITEM,	
  MAX(DATE UPDATING) as DATE UPDATING
Resident DataTable
Group by STORE,ITEM;

That keeps only the rows you want.

You can do another group by of that data to crate the sum or leva as it is so also have the value of each component.

Instead of an Inner Join you can also do a left join to add a flag (just add a field like "1 as flagLastUpdate"), and use this flag in set analysis to filter las update data by store and item.

This way you keep all data and can easily do calcualtions for last update.

View solution in original post

2 Replies
Highlighted

HI, if you do an inner join with

Inner Join (DataTable)
LOAD 
  STORE,
  ITEM,	
  MAX(DATE UPDATING) as DATE UPDATING
Resident DataTable
Group by STORE,ITEM;

That keeps only the rows you want.

You can do another group by of that data to crate the sum or leva as it is so also have the value of each component.

Instead of an Inner Join you can also do a left join to add a flag (just add a field like "1 as flagLastUpdate"), and use this flag in set analysis to filter las update data by store and item.

This way you keep all data and can easily do calcualtions for last update.

View solution in original post

Highlighted
Creator
Creator

thank you