Getting the latest customer name in loading script

[razvan_brais]

Hi guys ,

 I need some help. 

 I need to get the latest customer name from a table. I need to do this in loading script. 

The data looks something like this:

Customer ID	Customer Name	Reporting date
1	Test1	01/12/2019
1	Test2	01/01/2020
2	Test4	01/11/2019
2	Test3	01/02/2020

The result should be this:

Customer name : Test2 and Test3 because those names are the latest.

Can you please help me?

Thank you,

Razvan

[lironbaram · Accepted solution]

Hi 

your script can look like this :

Table:
Load Customer ID,
     Customer Name,
     Reporting date
From XXX;
//////////finding the max date for customer////
CustomerTemp:
load Customer ID,
     max([Reporting date]) as Reporting date
resident Table
group by [Customer ID];
left join (CustomerTemp)
load Customer ID,
     Customer Name
resident Table;

///////////////adding the latest name to main table 
left join (Table)
load [Customer ID],
     [Customer Name] as CustomerUpdateName
resident CustomerTemp;

drop table CustomerTemp;

[tresesco]

Try using firstsortedvalue().

[rwunderlich]

I would typically script it like this. Assume your data has already been loaded into a table named "Customers"

Inner Join (Customers)
LOAD
  [Customer ID],
  max([Reporting date]) as [Reporting date]
Resident Customers
Group by [Customer ID]
;

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[razvan_brais]

Hy all. Thank you for your help.

 I used @lironbaram solution and it worked. 

 Thank you again.

Razvan