topic Load with 3 conditions from 2 tables in QlikView

Load with 3 conditions from 2 tables

Michiel_QV_Fan — Tue, 29 Sep 2009 19:30:45 GMT

Hello,

I'm trying to develop a load script.

It has to do the following:

Load data from CATSDB. CATSDB has 2 field which are used for the key measurement Hours.

Load data from AUFK. AUFK has 1 field which is used for the key measurement Hours.

I tried this script:

LOAD
WORKDATE,
AWART,
applymap('AUFK_MAP', RAUFNR) as Ordersoort,
CATSHOURS as Uren2030,
PERNR,
RAUFNR as Ontvangen_order,
STATUS;
SQL SELECT *
FROM CATSDB where AWART in ('Y800', 'Y801', 'Y802', 'Y803', 'Y820', 'Y821', 'Y900') and STATUS IN ('20','30');;

which works OK for the AWART and STATUS but not for the mapped value from line 3.

Unfortunately, this value is essential for the calculation.

Adding the mapping statement to the where clause doesn't work.

Anyone an idea to solve this?

Thanks! [:)]

Load with 3 conditions from 2 tables

Tue, 29 Sep 2009 23:28:57 GMT

can't you filter by RAUFNR instead of the mapped result?

Load with 3 conditions from 2 tables

Anonymous — Wed, 30 Sep 2009 02:02:19 GMT

1 It is not clear if you have a map here, and if it is created correctly. Your LOAD statement assumes that there is script preceeding it:
AUFK_MAP:
MAPPING LOAD DISTINCT
Field1,
Field2;
SQL SELECT
...
FROM AUFK.

2. Applymap can work only in LOAD, but not in SELECT:
LOAD
...
WHERE ...applymap()...;
SQL SELECT...

Load with 3 conditions from 2 tables

Michiel_QV_Fan — Wed, 30 Sep 2009 13:07:12 GMT

No, the result of the mapping is new data.

Thanks.

Load with 3 conditions from 2 tables

Michiel_QV_Fan — Wed, 30 Sep 2009 13:09:49 GMT


Michael Solomovich wrote:
1 It is not clear if you have a map here, and if it is created correctly. Your LOAD statement assumes that there is script preceeding it:
AUFK_MAP:
MAPPING LOAD DISTINCT
 Field1,
 Field2;
SQL SELECT
...
FROM AUFK.
2. Applymap can work only in LOAD, but not in SELECT:
LOAD
...
WHERE ...applymap()...;
SQL SELECT... <div></div>

You are right. Unfortunately mapping doesn't work in Select..... I have to find another way.

Thanks