topic Re: Do...loop for loading from unstructured table in QlikView

Do...loop for loading from unstructured table

ingoniclas — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Do...loop for loading from unstructured table

Clever_Anjos — Wed, 06 Sep 2017 13:51:10 GMT

Can you share a sample of your excel file?

Re: Do...loop for loading from unstructured table

sunny_talwar — Wed, 06 Sep 2017 13:51:47 GMT

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

Re: Do...loop for loading from unstructured table

ingoniclas — Wed, 06 Sep 2017 13:55:14 GMT

Sure. Please find it attached.

Re: Do...loop for loading from unstructured table

sunny_talwar — Wed, 06 Sep 2017 14:01:16 GMT

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;

Re: Do...loop for loading from unstructured table

Clever_Anjos — Wed, 06 Sep 2017 14:02:46 GMT

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;

Re: Do...loop for loading from unstructured table

ingoniclas — Wed, 06 Sep 2017 14:10:01 GMT

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.

Re: Do...loop for loading from unstructured table

sunny_talwar — Wed, 06 Sep 2017 14:12:25 GMT

May be Clever's solution

Re: Do...loop for loading from unstructured table

ingoniclas — Wed, 06 Sep 2017 14:13:05 GMT

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

Re: Do...loop for loading from unstructured table

ingoniclas — Wed, 06 Sep 2017 16:07:10 GMT

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