topic Re: date granularity in script and storing as table in QlikView

date granularity in script and storing as table

manoranjan_d — Tue, 31 May 2016 11:03:15 GMT

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

Re: date granularity in script and storing as table

Peter_Cammaert — Tue, 31 May 2016 11:12:11 GMT

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

Re: date granularity in script and storing as table

Peter_Cammaert — Tue, 31 May 2016 11:13:11 GMT

Also, and to avoid misunderstandings, can you add a table with the required output for this particular data sample? Thanks,

Peter

Re: date granularity in script and storing as table

manoranjan_d — Tue, 31 May 2016 11:20:16 GMT

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.

Re: date granularity in script and storing as table

Kushal_Chawda — Tue, 31 May 2016 11:25:38 GMT

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;

Re: date granularity in script and storing as table

manoranjan_d — Tue, 31 May 2016 11:30:05 GMT

if the key1 is repeating in xth week of 2015 and xth of 2016, i need the max date for both years.

Re: date granularity in script and storing as table

manoranjan_d — Tue, 31 May 2016 11:30:58 GMT

this is only for the date max i need the separate table for week,month quater and year.

Re: date granularity in script and storing as table

Kushal_Chawda — Tue, 31 May 2016 11:38:14 GMT

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;

Re: date granularity in script and storing as table

Peter_Cammaert — Tue, 31 May 2016 11:46:28 GMT

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

Re: date granularity in script and storing as table

manoranjan_d — Tue, 31 May 2016 11:48:05 GMT

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.

Re: date granularity in script and storing as table

Peter_Cammaert — Tue, 31 May 2016 11:48:13 GMT

If you want three different tables, replace the JOIN of the second GROUP BY LOAD with a new table name and give the Month and Year fields unique names.

Best,

Peter

Re: date granularity in script and storing as table

manoranjan_d — Tue, 31 May 2016 12:08:03 GMT

peter u r almost right there but quater is missing, let me check this,

below s the output required.

Key	Created date	amount	Weeknumber
Key1	14-Oct-16	1564	xx week
Key1	14-Oct-15	1564	xx week
key2	15-Oct-16	45455	xx week
key2	16-Oct-16	100	xx week
key2	12-Nov-16	15456	xx week
key2	13-Nov-16	1564	xx week
key2	16-Oct-15	100	xx week
key2	13-Nov-15	1564	xx week
key3	19-Nov-16	1564	xx week
key3	14-Nov-15	45455	xx week
key3	19-Nov-15	1564	xx week

Quarter output
Key	Created date	amount
Key1	14-Oct-16	1564
Key1	14-Oct-15	1564
key2	13-Nov-16	1564
key2	13-Nov-15	1564
key3	19-Nov-16	1564
key3	19-Nov-15	1564

Year output
Key	Created date	amount
Key1	14-Oct-16	1564
Key1	14-Oct-15	1564
key2	13-Nov-16	1564
key2	13-Nov-15	1564
key3	19-Nov-16	1564
key3	19-Nov-15	1564

Re: date granularity in script and storing as table

manoranjan_d — Tue, 31 May 2016 12:08:23 GMT

month output
Key	Created date	amount
Key1	14-Oct-16	1564
Key1	14-Oct-15	1564
key2	16-Oct-16	100
key2	13-Nov-16	1564
key2	16-Oct-15	100
key2	13-Nov-15	1564
key3	19-Nov-16	1564
key3	19-Nov-15	1564

Re: date granularity in script and storing as table

manoranjan_d — Tue, 31 May 2016 12:24:32 GMT

peter i cant see the quater wise data

Re: date granularity in script and storing as table

Peter_Cammaert — Tue, 31 May 2016 12:30:16 GMT

All data matches, except for the weeks. How do you calculate the week number? Not by using 'x'-numbers I guess

Re: date granularity in script and storing as table

manoranjan_d — Tue, 31 May 2016 17:05:35 GMT

I thought the week start from Sunday, so it got mismatch, i agree that week number is calculated from monday,

I cant take manually calculation for the weeknumber, so to show the output i just shown it as xxx

can I know quaterly wise max date data?

Re: date granularity in script and storing as table

manoranjan_d — Wed, 01 Jun 2016 06:59:52 GMT

Hi peter,

I m waiting for your reply