Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
akpofureenughwu
Contributor III

Date Manipulation

Good day everyone,

Hope this meets you well.

I have a set of data that looks like this:

Repair:

YearMonthAmountRemarks
2016January10,000
2016March23,000
2016September40,000

TV Payment:

YearMonthAmountRemarks
20161000
2016March1500
2016September40,000

Supply

YearMonthAmountRemarks
2016January1
2016March23
2016September40

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

1 Solution

Accepted Solutions

Re: Date Manipulation

Hi,

one solution might be:

QlikCommunity_Thread_251410_Pic1.JPG

QlikCommunity_Thread_251410_Pic2.JPG

QlikCommunity_Thread_251410_Pic3.JPG

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

View solution in original post

7 Replies
Partner
Partner

Re: Date Manipulation

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.

akpofureenughwu
Contributor III

Re: Date Manipulation

Thanks Coen for your response..

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

Regards

Akpofure

annafuksa1
Contributor III

Re: Date Manipulation

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

akpofureenughwu
Contributor III

Re: Date Manipulation

Thanks Anna,

Still Have some grey spots on your solution

annafuksa1
Contributor III

Re: Date Manipulation

what do you mean ?

Re: Date Manipulation

Hi,

one solution might be:

QlikCommunity_Thread_251410_Pic1.JPG

QlikCommunity_Thread_251410_Pic2.JPG

QlikCommunity_Thread_251410_Pic3.JPG

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

View solution in original post

Re: Date Manipulation

Please close this thread if your question is answered:

Qlik Community Tip: Marking Replies as Correct or Helpful

thanks

regards

Marco