topic Re: Load Script with Mapping-Load-Filter in QlikView

Load Script with Mapping-Load-Filter

Tue, 17 Nov 2015 11:40:05 GMT

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

Re: Load Script with Mapping-Load-Filter

Mark_Little — Tue, 17 Nov 2015 11:47:34 GMT

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

Re: Load Script with Mapping-Load-Filter

Tue, 17 Nov 2015 11:52:24 GMT

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

Re: Load Script with Mapping-Load-Filter

jonathandienst — Tue, 17 Nov 2015 11:54:31 GMT

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 ...

Re: Load Script with Mapping-Load-Filter

maxgro — Tue, 17 Nov 2015 11:55:07 GMT

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'))