Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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);
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.
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
Thanks so much!
Thanks, dear!
Your solution will be also useful for my tasks..