Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have Sample Data like below. But in real there will be multiple products
Date | Product | Value |
10 Jan 16 | A | 100 |
11 Jan 16 | A | 200 |
12 Jan 16 | A | 150 |
11 Jan 16 | B | 200 |
12 Jan 16 | B | 250 |
13 Jan 16 | B | 100 |
I need the Output like below
Date | Product | Value | Latest Product Value |
10 Jan 16 | A | 100 | |
11 Jan 16 | A | 200 | |
12 Jan 16 | A | 150 | 150 |
11 Jan 16 | B | 200 | |
12 Jan 16 | B | 250 | |
13 Jan 16 | B | 100 | 100 |
Try this:
SET DateFormat='DD MMM YY';
Table:
LOAD Date,
Product,
Value
FROM
[https://community.qlik.com/thread/209896]
(html, codepage is 1252, embedded labels, table is @1);
Join (Table)
LOAD Product,
Date(Max(Date)) as Date,
FirstSortedValue(Value, -Date) as [Latest Product Value]
Resident Table
Group By Product;
Try this:
SET DateFormat='DD MMM YY';
Table:
LOAD Date,
Product,
Value
FROM
[https://community.qlik.com/thread/209896]
(html, codepage is 1252, embedded labels, table is @1);
Join (Table)
LOAD Product,
Date(Max(Date)) as Date,
FirstSortedValue(Value, -Date) as [Latest Product Value]
Resident Table
Group By Product;
dim:Product
Expression: =FirstSortedValue(Value, -Date)
one more way
If your Date is in Text Format then use Date# like below else no need.
Data:
Load date(date#(Date,'DD MMM YY'),'DD MMM YY') as Date
Product,
Value
From Table;
left join (Data)
Load Date(max(Date),'DD MMM YY') as Date,
Product,
1 as LatestProductValueFlag
Resident Data
Group By Product;
left join (Data)
Load Product,sum(Value) as [Latest Product Value]
Resident Data
where LatestProductValueFlag=1
Group By Product;
one more sample