How to load unstructured excel data into Qliksense

[Navars]

Hi All,

I have attached a sample data which i need to load into Qlik sense.

I am facing issue while loading the data into Qlik sense since its not in a good format.

Thanks in Advance.

[rwunderlich · Accepted solution]

Assuming you do have 12 fixed months of 2021, you could do it like below. This script makes use of the fact that when excel has duplicate column headers, the second column is referenced in script as name1, the third as name2 and so on. 

RawData:
LOAD *
FROM
sample.xlsx
(ooxml, embedded labels, header is 1 lines, table is Sheet1);

Accounts:
LOAD
     Number,
     Account,
     UD,
     [Account Description],
     Category,
     Subcategory,
     [UD6 Description],
     Region,
     Location
Resident RawData;

For i = 1 to 12
Let vSuffix = if($(i) = 1, '', Evaluate($(i)-1));
Monthly:
Load
    Account, 
    MakeDate(2021, $(i)) as Month,
    Budget$(vSuffix) as Budget,
    Spend$(vSuffix) as Spend,
    [Spend vs AOP$(vSuffix)] as [Spend vs AOP],
    [Finance Actual - Month End$(vSuffix)] as [Finance Actual - Month End],
    [Spend vs Actual$(vSuffix)] as [Spend vs Actual]
Resident RawData
; 
Next i

Drop table RawData;    

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[Navars]

Thank you so much Rob, Its working as per my requirement.

One more doubt, in future the data got extended like jan-22, feb-22,mar-22 etc.

will the existing code will work or do we need to tune the code accordingly.

Thanks In Advance!!

[rwunderlich]

You will need to adjust for future columns.  My sample assumed a fixed set of known columns. 

-Rob