Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Key | ID | Created date | amount | week |
Key1 | 1 | 10-Oct-16 | 100 | 1 |
Key1 | 2 | 11-Oct-16 | 2000 | 1 |
Key1 | 3 | 12-Oct-16 | 300 | 1 |
Key1 | 1 | 13-Oct-16 | 15456 | 2 |
Key1 | 2 | 14-Oct-16 | 1564 | 2 |
key2 | 3 | 15-Oct-16 | 45455 | 3 |
key2 | 1 | 16-Oct-16 | 100 | 3 |
key2 | 2 | 10-Nov-16 | 2000 | 1 |
key2 | 3 | 11-Nov-16 | 300 | 1 |
key2 | 1 | 12-Nov-16 | 15456 | 2 |
key2 | 2 | 13-Nov-16 | 1564 | 2 |
key3 | 3 | 14-Nov-16 | 45455 | 3 |
key3 | 1 | 15-Nov-16 | 100 | 4 |
key3 | 2 | 16-Nov-16 | 2000 | 4 |
key3 | 3 | 17-Nov-16 | 300 | 4 |
key3 | 1 | 18-Nov-16 | 15456 | 4 |
key3 | 2 | 19-Nov-16 | 1564 | 4 |
Above is my data, I need record count of key for the week, month, quarterly and yearly for max date
example
if the key1 is repeated 4 times in 1st week then record count of key1 shd be 1 and it shd retreive the maximum date only.
if the key1 s repeated 4 times in oct month then record count of key1 shd be 1 and it shd retreive the maximum date only, similar to , quarter then yearly
for week it shd be 52
month 12
quarterly 4
and yearly 1
How we ll do this date granularity in the script ?
Not sure what is the exact requirement, but please look at the attached
Script:
Table:
LOAD Key,
ID,
[Created date],
amount,
week,
WeekEnd([Created date]) as WeekEnd,
MonthEnd([Created date]) as MonthEnd,
QuarterEnd([Created date]) as QuarterEnd,
YearEnd([Created date]) as YearEnd
FROM
[https://community.qlik.com/thread/217568]
(html, codepage is 1252, embedded labels, table is @1);
i cant see the max date for key1
the max date for key1 for 1stweek 12oct2016 and for 2nd week is 14oct2016
the max date for key1 for oct month is 14 oct2016
the max date for key1 for quarterly is 14 oct2016
yearly is 14 oct2016
See the max date with their counts?
i cant get you can u say me clearly
Like this?