Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
Try using firstsortedvalue().
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