Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
My data looks like this as below.
Customer ¦ Product number ¦ Date
A 222222 10/11/2011
B 222222 11/11/2014
C 233334 10/07/2015
D 233334 10/08/2014
My output should be like this below.
So basically if the same product number then it should display only the latest date with the customer detail. No old record should be shown.
I tried couple of ways using expression but it shows all the records and only highlights the latest one/.
Please advise here.
Try this:
Dimension: [Product number]
Expression:
1) FirstSortedValue(Customer, -Date)
2) FirstSortedValue(Date, -Date)
Have a look at the attached:
Hi Rathish,
Maybe:
Table:
Load [Product number],
max(Date) as Date
Resident ...
Group by [Product number];
left join(Table)
Load
Customer,
[Product number],
Date
Resident...
if you have Date and Product number repeating for same Customer, use below formulas....
FirstSortedValue(DISTINCT Customer, -Date)
FirstSortedValue(DISTINCT Date, -Date)
I tried this but I am not getting filtered. its displays the same with all values
For this FirstSortedValue(Date, -Date) I get the difference in integers like 422336.. 43336 etcc.
Hi Both Manish and sunny is working. But iam getting this part
FirstSortedValue(Date, -Date)
FirstSortedValue(DISTINCT Date, -Date) for both as integers ..dates is not getting reflected..
Neetu 's need to check..
use this load script or see the attachment:
LOAD * Inline [
Customer, Product number, Date
A, 222222, 10/11/2011
B, 222222, 11/11/2014
C, 233334, 10/07/2015
D, 233334, 10/08/2014
];
TableOut:
Max(Date) as Date
Resident Table
left join
Load Customer, [Product number], Date
Resident Table;
Drop Table Table;
Date(FirstSortedValue(Date, -Date)) or
Date(FirstSortedValue(DISTINCT Date, -Date))
Hi Guys,
It works good. Their is another way also ..from the presentation tab we could use the date option.
