Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Navars
Creator
Creator

How to load unstructured excel data into Qliksense

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.

 

Labels (2)
1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

View solution in original post

3 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Creator
Creator
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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

-Rob