How to load only the most recent price - one row per ID, Country, Source

[#1]

Hi All,

Would you be so kind to help me with an issue I'm currently facing.

I'm new to QlikView but I tried couple of things I read on this forum such as

Max(Date) with group by but it's loading all rows not only the most recent one.

AllPrices:

ID	Country	Source	Price	Date
1	DE	spar-express.de	90	03/01/2017
1	DE	spar-express.de	75	08/02/2017
1	GB	tesco.co.uk	100	15/01/2017
1	GB	tesco.co.uk	250	01/02/2017
2	DE	intersport.de	320	15/12/2016
2	DE	intersport.de	420	02/02/2017
2	GB	asda.co.uk	160	01/01/2017
2	GB	asda.co.uk	220	06/02/2017

DesiredOutcome:

ID	Country	Source	Price	MostRecentDate
1	DE	spar-express.de	75	08/02/2017
1	GB	tesco.co.uk	250	01/02/2017
2	DE	intersport.de	420	02/02/2017
2	GB	asda.co.uk	220	06/02/2017

Script I currently have:

AllPrices:

LOAD   

          ID,

          Country,

          Source,

          Price,

          Date(Max(Date),'YYYY-MM-DD') as MostRecentDate

FROM

AllPrices.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

group by ID, Country, Source, Price;

Thanks in advance for your help.

Regards,

Amko

[dsharmaqv]

try this

Get most-recent value of one field based on dat... | Qlik Community

hope it resolves your issue

[rahulpawarb · Accepted solution]

Hello Amko,

Trust that you are doing great!

Please refer below given draft version of script:

AllPrices:
LOAD ID, 
     Country, 
     Source, 
     Price, 
     Date(Date) AS Date
FROM
AllPrices.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
INNER JOIN (AllPrices)
LatestPrices:
LOAD ID, 
     Country, 
     Source,  
     Date(Max(Date)) AS Date
Resident AllPrices
Group By ID, 
     Country, 
     Source;

Also refer the sample application attached herewith.

Regards!

Rahul

[adamdavi3s]

The problem is that your price changes as well, that is why the group by doesn't work.

I knocked up this code which does the job

Table:
LOAD *, Hash128(ID, Country, Source,Date) as uniquecolumn
FROM
[https://community.qlik.com/thread/251116]
(html, codepage is 1252, embedded labels, table is @1);


NoConcatenate


maxdate:
LOAD Hash128(ID, Country, Source,MostRecentDate) as keepid;
LOAD  DISTINCT
          ID, Country, Source,
          Date(Max(Date),'YYYY-MM-DD') as MostRecentDate
resident Table
group by ID, Country, Source;


NoConcatenate


finaltable:


load *
resident Table
where Exists (keepid,uniquecolumn);


drop tables Table, maxdate;

[adamdavi3s]

Yeah the above example is much simpler, my brain over complicated it!

[antoniotiman]

Hi Amko,

Temp:
LOAD ID,
Country,
Source,
Price,
Date
FROM
"https://community.qlik.com/message/1219395"
(html, codepage is 1252, embedded labels, table is @1);

Load ID,Country,Source,Max(Date) as Date,
FirstSortedValue(Price,-Date) as Price1
Resident Temp
Group By ID,Country,Source;

Drop Table Temp;

Regards,

Antonio

[#7]

wow guys
you solved my nightmare

thank you Deepak, Rahul, Adam and Antonio

[#8]

Hi All,

Is it possible to have the same result in a straight table once all data has been uploaded?

1. AllPrices: 
2. LOAD ID,  
3.      Country,  
4.      Source,  
5.      Price,  
6.      Date(Date) AS Date 
7. FROM 
8. AllPrices.csv 
9. (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

since with the above example if I merge data with another table I don't have the most recent price anymore but all prices.

[rahulpawarb]

Hello Amko,

Yes, you can! If you refer the table AllPrices given in my prior response has most recent prices; you can add those fields to straight table.

Hope this will be helpful.

Regards!

Rahul

[#10]

Hi Rahul,

All is fine I was using 2 date columns

In allprices and latest prices date column wasn't of the same name so I didn't have unique rows.

Thanks again.

Regards,

Amko