Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Key | Created date | amount |
Key1 | 10-Oct-16 | 100 |
Key1 | 11-Oct-16 | 2000 |
Key1 | 12-Oct-16 | 300 |
Key1 | 13-Oct-16 | 15456 |
Key1 | 14-Oct-16 | 1564 |
key2 | 15-Oct-16 | 45455 |
key2 | 16-Oct-16 | 100 |
key2 | 10-Nov-16 | 2000 |
key2 | 11-Nov-16 | 300 |
key2 | 12-Nov-16 | 15456 |
key2 | 13-Nov-16 | 1564 |
key3 | 14-Nov-16 | 45455 |
key3 | 15-Nov-16 | 100 |
key3 | 16-Nov-16 | 2000 |
key3 | 17-Nov-16 | 300 |
key3 | 18-Nov-16 | 15456 |
key3 | 19-Nov-16 | 1564 |
Key1 | 10-Oct-15 | 100 |
Key1 | 11-Oct-15 | 2000 |
Key1 | 12-Oct-15 | 300 |
Key1 | 13-Oct-15 | 15456 |
Key1 | 14-Oct-15 | 1564 |
key2 | 15-Oct-15 | 45455 |
key2 | 16-Oct-15 | 100 |
key2 | 10-Nov-15 | 2000 |
key2 | 11-Nov-15 | 300 |
key2 | 12-Nov-15 | 15456 |
key2 | 13-Nov-15 | 1564 |
key3 | 14-Nov-15 | 45455 |
key3 | 15-Nov-15 | 100 |
key3 | 16-Nov-15 | 2000 |
key3 | 17-Nov-15 | 300 |
key3 | 18-Nov-15 | 15456 |
key3 | 19-Nov-15 | 1564 |
hi above is my data,
I need the distinct key with max date for the week , for the month , for the quarter , for the year.
These (week, month, quater and year) has to be retrieved form the above table and these has to stored as separate table as week, year, quarter, month.
can you help me how these can achieved in the script level
Do you want to keep the max date for every dimension, or do you want to pick the date with the maximum amount for each period?
Peter
Also, and to avoid misunderstandings, can you add a table with the required output for this particular data sample? Thanks,
Peter
pick the data with maximum date only.
i will say an example in the data
Key | Created date | amount |
Key1 | 10-Oct-16 | 100 |
Key1 | 11-Oct-16 | 2000 |
Key1 | 12-Oct-16 | 300 |
Key1 | 13-Oct-16 | 15456 |
Key1 | 14-Oct-16 | 1564 |
the key1 has been repeated 5 times in a week , so the key count should not be 5 times, it should be count as 1 with max date
so the output for the oct xth week is key1 14oct2016 1564 .
similar to month, quater and year.
Data:
LOAD Key,
CreatedDate,
Amount
FROM table;
left join(Data)
LOAD key,
date(max(CreatedDate)) as CreatedDate,
1 as MaxDateFlag
resident Data
group by key;
Final:
noconcatenate
LOAD *
resident Data
where MaxDateFlag=1;
drop table Data;
if the key1 is repeating in xth week of 2015 and xth of 2016, i need the max date for both years.
this is only for the date max i need the separate table for week,month quater and year.
Why you need separate table?
Just try to create the field in table itself
Data:
LOAD Key,
CreatedDate,
Amount
FROM table;
left join(Data)
LOAD key,
date(max(CreatedDate)) as CreatedDate,
1 as MaxDateFlag
resident Data
group by key;
Final:
noconcatenate
LOAD *,
Year(CreatedDate) as Year,
month(CreatedDate) as Month,
Week(CreatedDate) as Week,
'Q' & ceil(month(CreatedDate)/3) as Quarter
resident Data
where MaxDateFlag=1;
drop table Data;
Something like this?
I don't manage to put everything in one table because week numbers may cross month borders. Two tables at the least. You can proceed from here, as I don't think this can be used as-is in whatever document you're preparing.
Best,
Peter
Hi chawda
i fu put max date then it ll take only one date, suppose if the key 1 is repeating on the next month (nov) then it ll take only the november month only, then it wont take the oct weeks max date.