Qlik Community

Ask a Question

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Partner
Partner

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
Partner
Partner

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
Specialist
Specialist

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.

Partner
Partner

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

Partner
Partner

Fantastic, thanks very much Gabriela

Partner
Partner

Thanks Chris

Partner
Partner

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 ?

Partner
Partner

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

Specialist
Specialist

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.