Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a table with a set of unique IDs, shared IDs, and Dates. When loading the data, I'm attempting to add a calculated column which lists the maximum date associated with the shared IDs for each unique ID. I've been able to determine these values in excel using an array formula, but I'm at a loss as to how I should proceed with loading and modifying this data using QlikView. There may be any number of unique IDs associated with the same Shared ID.
For example, something that inputs the 3 leftmost columns and will be able to output the maximum date.
Unique ID | Shared ID | Date | *Maximum Date* |
---|---|---|---|
1 | A | 1/1/2017 | 1/1/2017 |
2 | A | 12/12/2016 | 1/1/2017 |
3 | B | 2/2/2017 | 2/2/2017 |
4 | B | 11/11/2016 | 2/2/2017 |
Thank you in advance for your help!
Another way to do this:
Table:
LOAD * INLINE [
Unique ID, Shared ID, Date
1, A, 1/1/2017
2, A, 12/12/2016
3, B, 2/2/2017
4, B, 11/11/2016
];
FinalTable:
LOAD *,
If([Shared ID] = Previous([Shared ID]), Peek('MaxDate'), Date) as MaxDate
Resident Table
Order By [Shared ID], Date desc;
DROP Table Table;
hi
assuming your source table has three fields try this
Table
load [Unique ID],[shared ID],[Date]
FROM your Table
left join (Table )
load [shared ID],
max([Date]) AS Maximum_Date
resident Table
Another way to do this:
Table:
LOAD * INLINE [
Unique ID, Shared ID, Date
1, A, 1/1/2017
2, A, 12/12/2016
3, B, 2/2/2017
4, B, 11/11/2016
];
FinalTable:
LOAD *,
If([Shared ID] = Previous([Shared ID]), Peek('MaxDate'), Date) as MaxDate
Resident Table
Order By [Shared ID], Date desc;
DROP Table Table;
I've tried this solution a number of ways but it says 'Invalid expression' highlighting from left join onward. The other answer did work however. Thank you
Thank you this worked perfectly!
Some minor corrections
Table:
LOAD [Unique ID],
[shared ID],
[Date]
FROM yourTable;
Left Join (Table)
LOAD [shared ID],
Max([Date]) as Maximum_Date
Resident Table
Group By [shared ID];