Load Script with Mapping-Load-Filter

[#1]

Hi,

I hope someone can help me. I want to load from the Oracle Database.

The normal LoadScript is working.

My challenge is that I want to load only some data. For that I created a MappingLoad with 2 columns (repbasis, repbasis_used) . But the filter does not work. I get an error-message.

oas_balance:
LOAD
CMPCODE  as cmpcode,
EL1  as el1 ,
REPBASIS  as repbasis,
"FULL_VALUE"  as "full_value",

ApplyMap('Map_Repbasis',REPBASIS) as  Repbasis_used
;
SQL SELECT *
FROM "OAS_BALANCE"
WHERE CMPCODE LIKE '$(varCMPCODE)'
//AND Repbasis_used = 'y' //not working
AND YR >= '$(varMinJahrFix)'
AND YR <= '$(varMaxJahrFix)' ;
store oas_balance into $(vDataQVD)oas_balance.qvd;

How can I load from the Database with a filter, that is not in the Database?

I cannot list a repbasis entries, that I want to load (because this can Change).

Thanks for your help in advance.

Chris

[Mark_Little]

Hi,

The problem is table load from bottom to top,

So the SQL runs, so the field you are mapping doesn't exist at that point.

Maybe load the the repbasis you want first and load where exists?

Mark

[#3]

Hi Mark,

at first I loaded the Mapping load. Afterwards I want to load the balances.

But the Problem is, that the filter is not repbasis (is in the database) but repbasis_used.

I integrate that field during the load but I also want this field to act as a filter.

Do you have any idea, how I can realise that?

Chris

[jonathandienst]

Repbasis_used does not exist in the database, so your cannot do the filtering like that. Either do the mapping and bring in Repbasis_used in the database with a SQL join, or you will have to bring in all the data in the SQL part and filter the LOAD part, like:

     LOAD ...

          ApplyMap('Map_Repbasis',REPBASIS) as  Repbasis_used

     Where ApplyMap('Map_Repbasis',REPBASIS) = 'y'

     ;

     SQL SELECT ...

[maxgro]

you can filter at the Qlik side (pay attention that you get all rows from Oracle and the filter is in Qlik)

LOAD
CMPCODE  as cmpcode,
EL1  as el1 ,
REPBASIS  as repbasis,
"FULL_VALUE"  as "full_value",

ApplyMap('Map_Repbasis',REPBASIS) as  Repbasis_used

where ApplyMap('Map_Repbasis',REPBASIS) = 'y'

;

SQL select ....

or you have few values to filter you can set the filter in a Qlik variable (var, usually using concat)

and use that variable in Oracle

where Repbabis_used in ('$(var'))