topic Re: Left keep function in QlikView

Left keep function

Tue, 17 Jun 2014 14:05:22 GMT

Hello Experts

I used a "Left keep" function to link the two tables in a way that when datum_auftrag <> null, values are shown. if datum_auftrag = null, all other values are excluded. Not only in table "job" but also the fields that are linked from table "jleistg".

SQL SELECT "job_nummer",

jobnr,

"datum_auftrag",

Year ("datum_auftrag") as Jahr,

Month ("datum_auftrag") as Monat,

jobkategorie

FROM EASY.job where "datum_auftrag" is not null;

LEFT KEEP (job)

load *, [auf_kurs] * [auf_wert] / 100 as [Auftragswert];

SQL SELECT "auf_datum",

"auf_fw",

"auf_kurs",

"auf_wert",

bez as Leistungstext,

jobnr,

"auf_anzahl",

ltyp,

storniert,

optional,

lartnr

FROM EASY.jleistg where storniert = 0 and optional = 0 and ltyp <> 0;

Now I would like to use another table with a selection criteria to exclude all linked vaues in the two tables above:

SQL SELECT bez as Jobkriterium,

beznr as Jobkritnr

FROM EASY.divbez where Jobkriterium <> 'Internes Projekt';

Where do I have to set the "Left keep" keep in this case? I do not seem to get it right...

Thank you so much for your help...

Re: Left keep function

Tue, 17 Jun 2014 14:22:37 GMT

Hi Jan

LEFT KEEP will keep table but operate similar as a join.

If you don't precise any table it will be based on the previous table.

If you want you can precise a table wich was loaded many instruction before with

LEFT KEEP (TableName to consider)

LOAD ....

SQL SELECT ....

best regards

Chris

Re: Left keep function

Tue, 17 Jun 2014 14:27:32 GMT

Hi christianj

Thank you for your reply. So if I want to join my last table "divbez" with the other two, can I write two Left keep orders?

LEFT KEEP (Job)

LEFT KEEP (jleistg)

LOAD ....

SQL SELECT ....

Re: Left keep function

Tue, 17 Jun 2014 14:39:19 GMT

In other words how do I script multiple LEPT KEEP orders to one table?

Re: Left keep function

Tue, 17 Jun 2014 14:42:00 GMT

You would do them done at a time,

LEFT KEEP (Job)

LOAD ....

SQL SELECT ....

LEFT KEEP (jleistg)

LOAD ....

SQL SELECT ....

Re: Left keep function

Tue, 17 Jun 2014 14:47:20 GMT

Jan

as far as i know the LEFT KEEP will operate only on 1 table, so you'll have to do

T3:

LEFT KEEP (job)

LOAD ....

T4:

LEFT KEEP (jleistg)

LOAD ... resident T3

best regards

Chris