Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
hopkinsc
Partner - Specialist III
Partner - Specialist III

Help loading in excel file with sections

Hi all,

I need to load in the attached excel file but i don't know how to.

within the sheet the data is split into sections.

i.e.

Item

Manufacturing

Freight

Duty

Labour

Distribution

Manufacturing

Freight

Duty

Labour

Destuffing

Manufacturing

Freight

Duty

Labour

etc

what i want is to use the bold text as the section name and the values beneath as another field.

so id want it loaded something like..

Section, Value

Item, Manufacturing

Item, Freight

Item, Duty

Item, Labour

Distribution, Manufacturing

Distribution, Freight

Distribution, Duty

Distribution, Labour

Destuffing, Manufacturing

Destuffing, Freight

Destuffing, Duty

Destuffing, Labour



Can anyone suggest a way of doing this please without changing the source file?


1 Solution

Accepted Solutions
gmoraleswit
Partner - Creator II
Partner - Creator II

Hello,

Not sure if this is what you need but I hope it helps you:

I first identified the Sections and the Values:

Temp:

LOAD if(RowNo()=1 or len(B)=0,A) as Section,

if(RowNo()>1 and len(B)>0,A) as Value,

// A,

     B,

     C,

     D,

     ...

     W,

     X,

     Y,

     Z,

     AA

FROM

Sample.xlsx

(ooxml, no labels);

Then I use the Fill option in the Transformation Step when you load the qvd:

Capture.PNG

Capture2.PNG

See QVW Attached.

Regards!

View solution in original post

7 Replies
chrismarlow
Specialist II
Specialist II

You can change your Excel load to ignore the headings then use a match & peek to populate your section & value fields from column A, you would then need to tidy up the loaded data (this will be very sensitive to breaking if the format changes at all & you also need to think about how you get names for columns B onwards ...

LOAD

If(Match(A,'Item','Distribution','% for Picking','Destuffing')>0,A,peek(Section,-1)) AS Section,

If(Match(A,'Item','Distribution','% for Picking','Destuffing')>0,Null(),A) AS Value;

LOAD

A

FROM [Sample.xlsx] (ooxml, no labels, table is [P&L Feeds]);

Regards,

Chris.

gmoraleswit
Partner - Creator II
Partner - Creator II

Hello,

Not sure if this is what you need but I hope it helps you:

I first identified the Sections and the Values:

Temp:

LOAD if(RowNo()=1 or len(B)=0,A) as Section,

if(RowNo()>1 and len(B)>0,A) as Value,

// A,

     B,

     C,

     D,

     ...

     W,

     X,

     Y,

     Z,

     AA

FROM

Sample.xlsx

(ooxml, no labels);

Then I use the Fill option in the Transformation Step when you load the qvd:

Capture.PNG

Capture2.PNG

See QVW Attached.

Regards!

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

Fantastic, thanks very much Gabriela

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

Thanks Chris

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

Hi Gabriela..

Just a quick question..

What you did worked but i need the column headings from column 3 onwards. as highlighted below..

Capture.JPG

Do you know how i can achieve this ?

gmoraleswit
Partner - Creator II
Partner - Creator II

Hi Chris,

The only way I could do this is by storing the data as a txt and then using the table load wizard to indicate the number header lines and the embedded labels

Capture.PNG

Then in the transformation step I deleted the lines in blank

Capture2.PNG

See attach qvw.

Regards!

Gabriela

chrismarlow
Specialist II
Specialist II

I prefer Gabriela's initial approach to mine, I had not seen the transform step in any detail, but think for the second piece maybe the peek can work better, so extending my code (or you could extend from the transformed steps);

temp:
LOAD
If(Match(A,'Item','Distribution','% for Picking','Destuffing')>0,A,peek(Section,-1)) AS Section,
If(Match(A,'Item','Distribution','% for Picking','Destuffing')>0,Null(),A) AS Value,
B;
LOAD
A ,
B
FROM [Sample.xlsx] (ooxml, no labels, table is [P&L Feeds]);

Let TitleB=peek('B',0);

final:
NoConcatenate
Load
Section,
Value,
B AS $(TitleB)
Resident temp;

Drop table temp;

Cheers,

Chris.