Loading unstructured format data from excel to Qliksense

[sn_9052]

Hi All,

Can any one help me out on below attached a sample unstructured format of an excel file,

where i need to load it into Qliksense. same Scenario iam facing in real time, just i changed the data.

When i try to load it ,QLiksense showing me column names as Name,Class,Segment,D,E,F.G,H

So any one can help me how to sort it out this loading .

Please find the attached sample excel

[NitinK7 · Accepted solution]

Please try to change header size and load 

see below screenshot

[sn_9052]

Hi Nitin,

Thanks for reply, and i was able to load data.

But iam facing an another issue, when i create pivot table data is not reflecting properly,  I mean to say.

Under ABC (Area) there should be XYZ(Subtopic) and Lag(Subtopic).

and when we expand XYZ , then A,B,C,D should come.

So, any helping with your suggestion

Thanks. 🙂

[QFabian · Accepted solution]

Hi @sn_9052 , please try this :

Data:
LOAD
recno() as Id,
if(rowno()=1, Area, if(isnull(Area), peek(Area), Area)) as Area,
if(rowno()=1, Topic, if(isnull(Topic), peek(Topic), Topic)) as Topic,
if(rowno()=1, Subtopic, if(isnull(Subtopic), peek(Subtopic), Subtopic)) as Subtopic,
if(rowno()=1, "Owning Team", if(isnull("Owning Team"), peek("Owning Team"), "Owning Team")) as "Owning Team",
if(rowno()=1, "Task Team", if(isnull("Task Team"), peek("Task Team"), "Task Team")) as "Task Team",
if(rowno()=1, COMMENTS, if(isnull(COMMENTS), peek(COMMENTS), COMMENTS)) as COMMENTS,
"1", "2", "3", "4", "5", "6",
"7", "8", "9", "10", "11", "12"
FROM [lib://desktop (fabian_fabian)/New Microsoft Excel Worksheet.xlsx]
(ooxml, embedded labels, header is 3 lines, table is Sheet1);

Data2:
crosstable(Month, Data, 7)
Load
*
Resident Data;
drop table Data;

[sn_9052]

Thank You so much for your reply.

It worked. Now the data is coming correctly.

If possible , will i get any explanation that  why u took rowno() and the conditions you took.

Thanks once again 🙂

[QFabian]

sure! sorry, should i did that before :

if(rowno()=1, Area, if(isnull(Area), peek(Area), Area)) as Area

if is the first record, put the value of 'Area' in the same record, but, if 'Area' field value is null, take the previous calculated value for 'Area' record, and if is not any of the prior two 'if', then keeps the value for the current record.

I hope I was clear.