Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
ingoniclas
Creator II
Creator II

Do...loop for loading from unstructured table

Hi there,

I want to load a bunch of tables in QV. These tables don't have the usual structure of a list (see 1st table shown below). They contain data on sales per retailer. The retailer with its unique ID (circled in red) always comes first, then each sales position (rows with "POSITION") that can be attributed to this retailer. When loading the tables I want to generate a decent list with the apropriate retailer ID next to each position as shown in the 2nd table below.

I know I should use the Do...loop, however, I am not sure how the script should look like in detail to get the desired result. Any ideas? Your help is much appreciated.

Loop_discussion.JPG

Many Thanks,

Ingo

1 Solution

Accepted Solutions
Clever_Anjos
Employee
Employee

Please try this and make your comments

t:

LOAD HEAD,

     HER,

     SER,

     [300200043],

     [DATA CENTER MUC],

     [13745],

     If(HEAD = 'RETAILER',HER,Peek('RETAILER_ID')) AS RETAILER_ID

FROM

(ooxml, embedded labels, table is Tabelle1);

NoConcatenate LOAD * Resident t where HEAD = 'POSITION';

Drop Table t;

View solution in original post

9 Replies
Clever_Anjos
Employee
Employee

Can you share a sample of your excel file?

sunny_talwar

Do you mind sharing some raw data (in Excel format)?

ingoniclas
Creator II
Creator II
Author

Sure. Please find it attached.

sunny_talwar

Try this

Table:

LOAD HEAD,

    HER,

    SER,

    [300200043],

    [DATA CENTER MUC],

    [13745],

    If(Left(HER, 1) = 8, HER, Peek('NEW_HER')) as NEW_HER

FROM

[..\..\Downloads\Sample_Original_Table.xlsx]

(ooxml, embedded labels, table is Tabelle1);

FinalTable:

LOAD NEW_HER as [RETAILER ID],

HEAD,

HER,

SER,

[300200043],

[DATA CENTER MUC],

[13745]

Resident Table

Where [300200043] = 'E';

DROP Table Table;

Capture.PNG

Clever_Anjos
Employee
Employee

Please try this and make your comments

t:

LOAD HEAD,

     HER,

     SER,

     [300200043],

     [DATA CENTER MUC],

     [13745],

     If(HEAD = 'RETAILER',HER,Peek('RETAILER_ID')) AS RETAILER_ID

FROM

(ooxml, embedded labels, table is Tabelle1);

NoConcatenate LOAD * Resident t where HEAD = 'POSITION';

Drop Table t;

ingoniclas
Creator II
Creator II
Author

Thanks. for the quick reply. This solves the problem for this specific table. However, please keep in mind that I want to load many tables from a folder with a loop (since every month new tables are added), and the retailers' ID don't always contain the number 8 at the beginning. I am afraid I need a more general approach.

sunny_talwar

May be Clever's solution

ingoniclas
Creator II
Creator II
Author

PS: There will not always be an E in the rows with the sales data.

ingoniclas
Creator II
Creator II
Author

That's it, it also works when I put this into the Do...loop. Great! Many thanks for your help!