Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

left keep

Hi experts

I would like to join the following 3 tables: job, jleistg, divbez. I managed to join job and jleistg in a way that if there is no value in "datum_auftrag" all other values that are linked are not shown. Now I would like to additionally link divbz in a way that if "Jobkriterium" = 'Internes Projekt all the linked values in all tables are not shown. How do I do this? I do not seem to get the link (left keep) right...

SQL SELECT bez as Jobkriterium,

    beznr as Jobkritnr

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

left Keep (divbez)

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)

SQL SELECT "auf_datum",

    "auf_fw",

    "auf_kurs",

    "auf_wert",

     "kv_fw",

    "kv_kurs",

    "kv_wert",

    bez as Leistungstext,

    jobnr,

    "auf_anzahl",

    ltyp,

    storniert,

    optional,

    lartnr 

FROM EASY.jleistg;

Thank you very much for your help!

6 Replies
MVP & Luminary
MVP & Luminary

Re: left keep

The fields Jobkriterium and Jobkritnr don't exist in the other tables, so your first left keep does not do anything at all. You're merely creating a cartesian product between the first table and the other two.


talk is cheap, supply exceeds demand
Luminary
Luminary

Re: Re: left keep

Gysbert - I don't think KEEP creates a Cartesian product if there is no Join Predicate. Were you referring to Left Join instead of Left Keep or am I missing something here?

Jan - Sorry, I don't fully understand the question. Do you really need KEEP instead of JOIN? As mentioned by Gysbert you don't have common fields/ join predicate to perform the KEEP operation. Please can you explain further or provide the sample QVW file?

Thanks,

DV

www.QlikShare.com

MVP & Luminary
MVP & Luminary

Re: Re: left keep

If tables cannot be associated because there are no common field names then you get a cartesian product as soon as you use fields from both tables in a chart or in an expression.


talk is cheap, supply exceeds demand
Luminary
Luminary

Re: Re: Re: left keep

Sorry Gysbert. Both of us might be saying samething.

See the attached QVW where we're not having join predicate and if I'm using LEFT KEEP it's not creating Cartesian Product instead keeping the tables as is...

Thanks,

DV

MVP & Luminary
MVP & Luminary

Re: Re: Re: left keep

Yeah, and you're probably saying it more correctly than I am


talk is cheap, supply exceeds demand
Not applicable

Re: left keep

Hi, Thank you for your reply. You are right, the fields in divbez do not exist in other fields. I link them through another table. I attached a screenshot of my table architecture. I generally have problems with the "Left keep" when I want to connect more than two tables. I also tried to fuse the ables using "outer join". This also did not do the job. The "feft keep" works perfectly for joining "job" and "jleistg". All values that are associated to a field that have no "auf_datum" are exlcuded. I would like to do the same with "Jobkriterium". All values that are ssociated to an entry in "Jobkriterium" lis 'Internes Projekt' should be excluded. Thank you so much for your help!ScreenShot107.jpg