topic Re: Read Multiple Tables with Different Filters & Concatenate & Store in QlikView

Solved

microwin88x — Thu, 24 Sep 2015 13:34:08 GMT

Solved

Re: Read Multiple Tables with Different Filters & Concatenate & Store

swuehl — Thu, 24 Sep 2015 13:41:03 GMT

Create a key like

LOAD

INDICATOR & REGION & VERSION as KEY,

...

loading your Excel file.

Then

LOAD * FROM MAIN

WHERE EXISTS(KEY, INDICATOR & REGION & VERSION);

Re: Read Multiple Tables with Different Filters & Concatenate & Store

microwin88x — Thu, 24 Sep 2015 14:16:25 GMT

The thing is that I believe that I would need to read the tables INDICATOR, REGION and VERSION multiple times. For example for table INDICATOR, 1st time I would filter by Sales and 2nd time by Margin. Or when I use the Where Exists I would automatically filter for all combinations from the Excel?

Re: Read Multiple Tables with Different Filters & Concatenate & Store

jonathandienst — Thu, 24 Sep 2015 14:26:58 GMT

If I understand you right, then you could do something like this:

Filters:

LOAD INDICATOR & '|' & REGION & '|' & VERSION As Key

From Filter.xlsx

(ooxml, ....);

For i = 0 To NoOfRows('Filters')

Let vKey = Peek('Key', i, 'Filters');

TStore:

NoConcatenate

LOAD * Resident MAIN

Where INDICATOR & '|' & REGION & '|' & VERSION = '$(vKey)';

STORE TStore into File_$(i).qvd (qvd);

DROP TABLE TStore;

Next

DROP Table Filters;

Re: Read Multiple Tables with Different Filters & Concatenate & Store

swuehl — Thu, 24 Sep 2015 15:02:30 GMT

I don't think that you need to read the dimension tables multiple times.

You can load these tables after the MAIN table load and then use ...WHERE EXISTS(INDICATOR); etc. or do a LEFT KEEP (MAIN) LOAD for the dimension tables.