Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
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
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.
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
Yeah, and you're probably saying it more correctly than I am
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!