topic Re: Help loading in excel file with sections in QlikView

Help loading in excel file with sections

hopkinsc — Tue, 28 Aug 2018 15:36:32 GMT

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?

Re: Help loading in excel file with sections

chrismarlow — Tue, 28 Aug 2018 16:21:22 GMT

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

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

Regards,

Chris.

Re: Help loading in excel file with sections

gmoraleswit — Tue, 28 Aug 2018 17:30:45 GMT

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,

...

FROM

Sample.xlsx

(ooxml, no labels);

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

See QVW Attached.

Regards!

Re: Help loading in excel file with sections

hopkinsc — Tue, 28 Aug 2018 17:55:44 GMT

Fantastic, thanks very much Gabriela

Re: Help loading in excel file with sections

hopkinsc — Tue, 28 Aug 2018 17:55:59 GMT

Thanks Chris

Re: Help loading in excel file with sections

hopkinsc — Wed, 29 Aug 2018 10:08:15 GMT

Hi Gabriela..

Just a quick question..

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

Do you know how i can achieve this ?

Re: Help loading in excel file with sections

gmoraleswit — Wed, 29 Aug 2018 15:03:28 GMT

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

Then in the transformation step I deleted the lines in blank

See attach qvw.

Regards!

Gabriela

Re: Help loading in excel file with sections

chrismarlow — Wed, 29 Aug 2018 16:16:43 GMT

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.