Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table where I have code number ,location ,region ,zone ,amount and date field.
Code no repeats ,
I want to have sum of amount with unique code no in a table and that should be at max of date.
Eg:
If code no 1235 is coming two times in a table and have two dates and two amounts associated then I want
Code no 1235 only for once in a table with sum of both amount associated with it and the date associated should be the max date.
Please help
Thanks
May be this:
Right Join (TableName)
LOAD [code number],
Sum(amount) as New_amount,
Date(Max(Date)) as Date
Resident TableName
Group By [code number]
in user interface (chart) you can do it with a straight table chart
dimension code no
expression sum(amount)
max(date)
I have written
Qlik:
Load code no,
Sum(amount) as amount,
Date(max(date)) as date
From
C:.....
Group by code no;
Test:
Load
Code no,
Amount,
Date,
Code no&date as codeanddatekey
Resident qlik,
Drop table qlik;
Left join(test)
My full table
But the code no which were earlier having multiple dates are no more associated with any date after this code.need max date to come for that code.
Thanks