Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

andreyfcdk91
New Contributor III

Two month period name in script

Colleagues, good day!

Tell me please, how in load script make fields for two-month period and two-month period number?

For example:

For Jan-Feb dates Field TwoMonthPeriodName must have value Jan-Feb, Field TwoMonthPeriodNumber must have value 1

Test files in attachment.

Thanks.

1 Solution

Accepted Solutions

Re: Two month period name in script

or you can use

Dual(IF(Mod(Num(Month(Date)),2) = 1,

  Month(Date) & '/' & Month(AddMonths(Date,1)),

  Month(AddMonths(Date,-1)) & '/' & Month(Date)),Month(Date)) as TwoMonthName

and

AutoNumber(TwoMonthName) as TwoMonthNumber respectively for above script.

5 Replies

Re: Two month period name in script

Data:

Load *, AutoNumber(TwoMonthName) as TwoMonthNumber;

LOAD

  Date(Date) as Date,

  Month(Date) as Month,

  IF(Mod(Num(Month(Date)),2) = 1,

  Month(Date) & '/' & Month(AddMonths(Date,1)),

  Month(AddMonths(Date,-1)) & '/' & Month(Date)) as TwoMonthName

FROM

Test.xlsx

(ooxml, embedded labels, table is Source1);

Re: Two month period name in script

or you can use

Dual(IF(Mod(Num(Month(Date)),2) = 1,

  Month(Date) & '/' & Month(AddMonths(Date,1)),

  Month(AddMonths(Date,-1)) & '/' & Month(Date)),Month(Date)) as TwoMonthName

and

AutoNumber(TwoMonthName) as TwoMonthNumber respectively for above script.

Re: Two month period name in script

Hi,

solution maybe could be also:

QlikCommunity_Thread_197472_Pic1.JPG

Source1:

LOAD Date,

    Dual(Month(AddMonths(Date,Even(Month(Date))))&'-'&Month(AddMonths(Date,-Odd(Month(Date)))),Ceil(Month(Date)/2)) as TwoMonthPeriod,

    Month(AddMonths(Date,Even(Month(Date))))&'-'&Month(AddMonths(Date,-Odd(Month(Date)))) as TwoMonthPeriodName,

    Ceil(Month(Date)/2) as TwoMonthPeriodNumber

FROM [https://community.qlik.com/servlet/JiveServlet/download/939069-203218/Test.xlsx] (ooxml, embedded labels, table is Source1);

I would prefer the dual solution, like already suggested by Manish, for its inherent sorting capability.

hope this helps

regards

Marco

andreyfcdk91
New Contributor III

Re: Two month period name in script

Thanks so much!

andreyfcdk91
New Contributor III

Re: Two month period name in script

Thanks, dear!

Your solution will be also useful for my tasks..

Community Browser