Do...loop for loading from unstructured table

[ingoniclas]

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.

Many Thanks,

Ingo

[Clever_Anjos]

Can you share a sample of your excel file?

[sunny_talwar]

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

[ingoniclas]

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;

[Clever_Anjos · Accepted solution]

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]

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]

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

[ingoniclas]

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