topic Re: Two month period name in script in QlikView

Two month period name in script

Anonymous — Tue, 22 Dec 2015 14:40:49 GMT

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.

Re: Two month period name in script

MK_QSL — Tue, 22 Dec 2015 15:00:01 GMT

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

MK_QSL — Tue, 22 Dec 2015 15:01:03 GMT

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

MarcoWedel — Wed, 23 Dec 2015 01:12:17 GMT

Hi,

solution maybe could be also:

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

Re: Two month period name in script

Anonymous — Wed, 23 Dec 2015 07:24:09 GMT

Thanks so much!

Re: Two month period name in script

Anonymous — Wed, 23 Dec 2015 07:24:44 GMT

Thanks, dear!

Your solution will be also useful for my tasks..