Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Load Script with Mapping-Load-Filter

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

4 Replies
mark6505
Partner - Specialist III
Partner - Specialist III

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

Not applicable
Author

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
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
maxgro
MVP
MVP

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