topic Re: Date Manipulation in QlikView

Date Manipulation

akpofureenughwu — Mon, 27 Feb 2017 12:32:49 GMT

Good day everyone,

Hope this meets you well.

I have a set of data that looks like this:

Repair:

Year	Month	Amount
2016	January	10,000
2016	March	23,000
2016	September	40,000

TV Payment:

Year	Month	Amount
2016		1000
2016	March	1500
2016	September	40,000

Supply

Year	Month	Amount
2016	January	1
2016	March	23
2016	September	40

I need to develop a dashboard with data. I would love to develop a date format: Year>Quarter> Month.. And I believe the primary key I should use is the year field.

Please share your thoughts on this

Regards

Re: Date Manipulation

cgdonders — Mon, 27 Feb 2017 14:37:18 GMT

Hi akpofureenughwure111

Try this:

Supply:

LOAD

Year,

MakeDate(Year,Month(Date#(Month,'MMM')),1) AS Date, //1st date of month

Month(Date#( Month,'MMM') ) as Month,

Amount

FROM ....

;

based on the date field you can create more Calendar fields. Or use the Date field as a key to a Calendar table.

Re: Date Manipulation

akpofureenughwu — Mon, 27 Feb 2017 14:46:02 GMT

Thanks Coen for your response..

I tried the scripts but I noticed there was an error message 'year' field not found.

Regards

Akpofure

Re: Date Manipulation

annafuksa1 — Mon, 27 Feb 2017 14:49:38 GMT

Use for example

load

Amount,

Remarks,

date(date#(Year&'/'&Month ,'YYYY/MMMM')) as Date,

'Q'&ceil( MOnth(date(date#(Year&'/'&Month ,'YYYY/MMMM')))/3) as Quarter,

Month(date#(Year&'/'&Month ,'YYYY/MMMM')) as Month,

Year(date#(Year&'/'&Month ,'YYYY/MMMM')) as Year

From Repair...;

for every table and I will recommend you create 1 fact table instead 3 tables .

Anna

Re: Date Manipulation

akpofureenughwu — Mon, 27 Feb 2017 16:04:40 GMT

Thanks Anna,

Still Have some grey spots on your solution

Re: Date Manipulation

annafuksa1 — Mon, 27 Feb 2017 16:08:48 GMT

what do you mean ?

Re: Date Manipulation

MarcoWedel — Mon, 27 Feb 2017 22:44:30 GMT

Hi,

one solution might be:

table1:
LOAD Year,
    Dual(Month,Month(Date#(Month,'MMMM'))) as Month,
    Amount,
    'Repair' as AmountType,
    Remarks
FROM [https://community.qlik.com/thread/251410] (html, codepage is 1252, embedded labels, table is @1);

LOAD Year,
    Dual(Month,Month(Date#(Month,'MMMM'))) as Month,
    Amount,
    'TV Payment' as AmountType,
    Remarks
FROM [https://community.qlik.com/thread/251410] (html, codepage is 1252, embedded labels, table is @2);

LOAD Year,
    Dual(Month,Month(Date#(Month,'MMMM'))) as Month,
    Amount,
    'Supply' as AmountType,
    Remarks
FROM [https://community.qlik.com/thread/251410] (html, codepage is 1252, embedded labels, table is @3);

Join
LOAD Distinct
    Year,
    Month,
    MonthName(MakeDate(Year,Month)) as MonthName,
    Dual('Q'&Ceil(Month/3),Ceil(Month/3)) as Quarter
Resident table1;

hope this helps

regards

Marco

Re: Date Manipulation

MarcoWedel — Wed, 01 Mar 2017 19:17:59 GMT

Please close this thread if your question is answered:

Qlik Community Tip: Marking Replies as Correct or Helpful

thanks

regards

Marco