Solved

[microwin88x]

Solved

[swuehl]

Create a key like

LOAD

     INDICATOR & REGION & VERSION as KEY,

     ...

loading your Excel file.

Then

LOAD * FROM MAIN

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

[microwin88x]

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?

[jonathandienst · Accepted solution]

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;

[swuehl]

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.