Merging Year from column into X Axis with Months

[SGood]

Hi,

I am importing from an excel spreadsheet, into #QlikView.  Along with a number of other columns, the table contains the following structure, with the Allocation year in a separate column from the months:

BP ID	Allocation Year	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
8882	2020	0	0	0	0.85	0.85	0.85	0.85	0.85	0.85	0.85	0.85	0.85
10037	2021	0	0	0	0	0	0	0	0	0	0	0	0.4
10037	2022	0.4	0.4	0.4	0.4	0.4	0.4	0.4	0.4	0.4	0.4	0.4	0.4
10054	2022	1	1	1	1	1	1	1	1	0	0	0	0
10603	2020	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15
10603	2021	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15
10603	2022	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15

However, I need the year merged in with the month columns, to output like this:

BP ID	Jan-20	Feb-20	Mar-20	Apr-20	May-20	Jun-20	Jul-20	Aug-20	Sep-20	Oct-20	Nov-20	Dec-20	Jan-21	Feb-21	Mar-21	Apr-21	May-21
8882	0	0	0	0.85	0.85	0.85	0.85	0.85	0.85	0.85	0.85	0.85	0.85	0.85	0.85	0.85	0.85
10037	0	0	0	0	0	0	0	0	0	0	0	0.4	0.4	0.4	0.4	0.4	0.4
10037	0.4	0.4	0.4	0.4	0.4	0.4	0.4	0.4	0.4	0.4	0.4	0.4	0.4	0.4	0.4	0.4	0.4
10054	1	1	1	1	1	1	1	1	0	0	0	0	1	2	3	4	5
10603	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15
10603	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15
10603	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15	0.15

Any suggestions on how to create script to load the data into this structure would be greatly appreciated.

Thank you.

Sally

[stevejoyce · Accepted solution]

I thought it could be done simpler, but this should work:

temp_data:
LOAD
"BP ID",
"Allocation Year",
Jan,
Feb,
Mar,
Apr,
May,
Jun,
Jul,
Aug,
Sep,
Oct,
Nov,
Dec
FROM https://community.qlik.com/t5/QlikView-App-Dev/Merging-Year-from-column-into-X-Axis-with-Months/td-p...
(html, utf8, embedded labels, table is @1);

final_data:
load "BP ID" as BP_ID
resident temp_data;

//loop through each month
for month_iter = 1 to 12

let vListOfTables = '';

let vMonthName = month($(month_iter) &'/'&'1'&'/'&'2000') ;

//do generic load for current month. This will create a table & column for each years value.
temp_generic:
generic load "BP ID", "Allocation Year", $(vMonthName)
resident temp_data;

//get list of tables created
for vTableNo = 0 to NoOfTables()
let vTableName = TableName($(vTableNo));

if subfield(vTableName, '.', 1) = 'temp_generic' then

Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;

End If

Next vTableNo

//join list of tables created to final_data table and name field as Year - Month
For each vTableName in $(vListOfTables)

let vTempFieldName = subfield('$(vTableName)', '.', 2);
let vNewFieldName = subfield('$(vTableName)', '.', 2) & ' - ' & '$(vMonthName)';

Left Join (final_data)
Load
"BP ID" as BP_ID
,[$(vTempFieldName)] as [$(vNewFieldName)]
Resident [$(vTableName)];

//drop generic table
Drop Table [$(vTableName)];

//do next table (i.e. year)
Next vTableName

//do next month
next month_iter

drop table temp_data;

;

[MarcoWedel]

Hi,

another solution might be:

table1:
CrossTable (Month, SomeFact, 2)
LOAD *
FROM [https://community.qlik.com/t5/QlikView-App-Dev/Merging-Year-from-column-into-X-Axis-with-Months/m-p/1831139] (html, codepage is 1252, embedded labels, table is @1);

table2:
LOAD [BP ID], 
     [Allocation Year],
     Month(Date#(Month,'MMM'))						as Month,
     Date#(Month&'-'&[Allocation Year],'MMM-YYYY')	as AllocationYearMonth,
     SomeFact
Resident table1;

DROP Table table1;

hope this helps

regards

Marco

[SGood]

Thanks, Steve, I've tried this, but not having much joy. 

First it was staying on Jan, then I realised it was because the date format was US and I am in the UK, so I had to swap the DD and MM in this line: let vMonthName = month($(month_iter) &'/'&'1'&'/'&'2000') .  

Having changed this when it gets to the second month, Feb 2020, it fails on the Left Join (final_date) line.  First it freezes, with the typical "Not responding"  message in the header, then it either crashes or I see the following script error (and I'm not cancelling anything). 

Action canceled by user
Left Join (final_data)
Load
"BP ID" as BP_ID
,[2020] as [2020 - Feb]
Resident [temp_generic.2020]

Thanks Sally.

[SGood]

Thanks Marco.  

I have used this loading all the data from my excel file, which includes another 43 columns.  All the data loads fine, however, I have tried to add additional columns to the main "SomeFact" table, you had created.  The dimensions in your original table were simply  "BP ID" and "AllocationYearMonth".  I've added BP Name as an additional Dimension: 1st dim:"BP ID", 2nd dim: "BP Name", 3rd dim: "AllocationYearMonth", but the pivot treats the 2nd dim as a column heading rather than a row heading, and it needs to be a row heading.  Would you know if it's possible to fix this?

Sally  

[stevejoyce]

Ok, you should just need to format this part into whatever your DateFormat variable is: month('1/'&$(month_iter)&'/'&'2000') .

But I think I over complicated it anyway.  I thought you wanted your data model structured like your 2nd table so you had a seperate field for each month-year.  If that's just your front end design, then it would be better to unpivot in the data model and use a pivot table like @MarcoWedel was saying.

[SGood]

Yes, you were correct, ideally I do want the data model structured like the second table.  🙂

I fixed the month format, as you described above.  However, following this amendment, it's failing when it starts to process February.

[stevejoyce]

Ok.  I'd try and take a look, looks good on my end :). 

Maybe a couple troubleshooting steps and if you can post the error screenshot.

1) can you make sure in a text box this formula month('1/'& '6' &'/'&'2000') returns June 1 2000?

2 )Can you add in a trace for some troubleshooting.

//loop through each month
for month_iter = 1 to 12

let vListOfTables = '';

let vMonthName = month('1/'&$(month_iter)&'/'&'2000') ;

TRACE vMonthName = $(vMonthName );

[SGood]

Hi Steve,

As suggested I created a text box and I entered the below formula.  As you can see, this works fine:

The edited code in the script looks like this:

When stepping through the code, it runs through '2020 - Jan', '2021 - Jan' and '2022 - Jan' no problem, but gets stuck on '2020 - Feb' at this point:

It eventually threw the following error:

Action canceled by user
Left Join (final_data)
Load
"BP ID" as BP_ID
,[2020] as [2020 - Feb]
Resident [temp_generic.2020]

I took a screenshot, but then my laptop simply shut itself down.  First time, it's done this at this point, typically I've been able to stop the script, or QV crashes.

I'm going to try running it again shortly, with all other apps switched off.

Thanks Sally.

[stevejoyce]

yea so vMonthName is returning null.  can you update your calculation to remove the leading 0 in your day parameter.

let vMonthName = month('1'& '/' & $(month_iter)  &'/'&'2000') ;  

If that doesnt work let me see if there is another way that can pull month name based on our iteration counter.

certainly this will work...

let vMonthName = pick($(month_iter) , 'Jan', 'Feb', 'Mar', 'Apr'...FINISH THE MONTHS);

or how about this, i dont think this should be impacted by different date formatting...

let vMonthName =month(makedate(2020, $(month_iter), 1));