Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Gysbert_Wassenaar

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
IAMDV
Luminary Alumni
Luminary Alumni

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

Gysbert_Wassenaar

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
IAMDV
Luminary Alumni
Luminary Alumni

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

Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
Not applicable
Author

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