Possible Left Join ?

MikeGaunt1 — Fri, 13 Dec 2024 13:40:39 GMT

Hi All,

So I have two Tables

MKDP & MDTB

I need to take all of the rows of field DTNUM in MKDP and get all of the Matching rows in MDTB, and then use the remaining columns from MDTB to build the data set.

The below is the code thus far

However when used the fields such as PLUMI from MDTB are blank

Any Help is greatly received

LIB CONNECT TO [VAR_REV_TRACKER:Snowflake_Sco_Prd]; [MKDP]: LOAD HVR_ROWID, SHSTOVAR_SYNC_TIMESTAMP, MANDT, DTART, MATNR, PLWRK, PLSCN, DTNUM, DSDAT, BDBKZ, SLKZ1, SLKZ2, SLKZ3, SLKZ4, SLKZ5, SLKZ6, SLKZ7, SLKZ8, VRKZ1, VRKZ2, VRKZ3, MTART, MEINS, DISST, BESKZ, SOBSL, SOBES, WRK02, DISMM, DISVF, DISPO, PLDIS, EKGRP, MTWZT, WEBAZ, BEAZT, FIXTR, MFHKZ, DISLS, LOSVF, LOSKZ, PERAZ, EISBE, MINBE, HOEBE, BSTMI, BSTMA, BSTFX, BSTRF, SUM01, SUM02, SUM03, SUM04, SUM05, NEGBS, MSGID, MSGAR, MSGNR, MSGV1, MSGV2, MSGV3, MSGV4, DISGR, PERIV, MRPPP, BDARF, LFRHY, RDPRF, BERW1, BERW2, KZAUS, AUSDT, NFMAT, AUSZ1, AUSZ2, AUSZ3, AUSZ4, AUSZ5, AUSZ6, AUSZ7, AUSZ8, BEADA, NAUKZ, SAUFT, KZPROMO, SHFLG, SHZET, FABKZ, MFXDT, BSKFL, MAABC, CFLAG, GRREL, RWPRO, SHPRO, AHDIS, BERW4, BADON, SGT_STATC, HANAON, GLCHANGETIME, ZHDCLTIMESTAMP; [MDKP]: SELECT "HVR_ROWID", "SHSTOVAR_SYNC_TIMESTAMP", "MANDT", "DTART", "MATNR", "PLWRK", "PLSCN", "DTNUM", "DSDAT", "BDBKZ", "SLKZ1", "SLKZ2", "SLKZ3", "SLKZ4", "SLKZ5", "SLKZ6", "SLKZ7", "SLKZ8", "VRKZ1", "VRKZ2", "VRKZ3", "MTART", "MEINS", "DISST", "BESKZ", "SOBSL", "SOBES", "WRK02", "DISMM", "DISVF", "DISPO", "PLDIS", "EKGRP", "MTWZT", "WEBAZ", "BEAZT", "FIXTR", "MFHKZ", "DISLS", "LOSVF", "LOSKZ", "PERAZ", "EISBE", "MINBE", "HOEBE", "BSTMI", "BSTMA", "BSTFX", "BSTRF", "SUM01", "SUM02", "SUM03", "SUM04", "SUM05", "NEGBS", "MSGID", "MSGAR", "MSGNR", "MSGV1", "MSGV2", "MSGV3", "MSGV4", "DISGR", "PERIV", "MRPPP", "BDARF", "LFRHY", "RDPRF", "BERW1", "BERW2", "KZAUS", "AUSDT", "NFMAT", "AUSZ1", "AUSZ2", "AUSZ3", "AUSZ4", "AUSZ5", "AUSZ6", "AUSZ7", "AUSZ8", "BEADA", "NAUKZ", "SAUFT", "KZPROMO", "SHFLG", "SHZET", "FABKZ", "MFXDT", "BSKFL", "MAABC", "CFLAG", "GRREL", "RWPRO", "SHPRO", "AHDIS", "BERW4", "BADON", "SGT_STATC", "HANAON", "GLCHANGETIME", "ZHDCLTIMESTAMP" FROM "SCO_PRD"."RAW_SAP"."MDKP" WHERE "PLWRK" IN ('0600', '0602', '5440', '5442', '4980', '3930', '544E'); MDTB: LIB CONNECT TO [VAR_REV_TRACKER:Snowflake_Sco_Prd]; Left Join ([MDKP]) LOAD HVR_ROWID, SHSTOVAR_SYNC_TIMESTAMP, MANDT, DTNUM, DTPOS, PLAKZ, PLANR, DAT00, SORT1, SORT2, DELKZ, VSTAT, VRFKZ, PLUMI, MNG01, MNG02, MNG03, DAT01, DAT02, DAT03, PERKZ, PRPER, WEBAZ, FIX01, FIX02, BAART, BESKZ, SOBES, UMSKZ, PLART, AUSSL, WRK02, LGORT, DELNR, DELPS, DELET, AUFVR, POSVR, EINVR, UKZ01, UMDAT, DEL12, BAUGR, DELVR, VERSB, OLDSL, RDMNG, PLAAB, TAG00, ABEKZ, MDFST, MDMNG, VERID, WRK01, SERNR, REVLV, DBSKZ, EXTNL, ADINF, RESLO, PRIO_URG, PRIO_REQ, MPNMT, GLCHANGETIME, ZHDCLTIMESTAMP; [MDTB]: SELECT "HVR_ROWID", "SHSTOVAR_SYNC_TIMESTAMP", "MANDT", "DTNUM", "DTPOS", "PLAKZ", "PLANR", "DAT00", "SORT1", "SORT2", "DELKZ", "VSTAT", "VRFKZ", "PLUMI", "MNG01", "MNG02", "MNG03", "DAT01", "DAT02", "DAT03", "PERKZ", "PRPER", "WEBAZ", "FIX01", "FIX02", "BAART", "BESKZ", "SOBES", "UMSKZ", "PLART", "AUSSL", "WRK02", "LGORT", "DELNR", "DELPS", "DELET", "AUFVR", "POSVR", "EINVR", "UKZ01", "UMDAT", "DEL12", "BAUGR", "DELVR", "VERSB", "OLDSL", "RDMNG", "PLAAB", "TAG00", "ABEKZ", "MDFST", "MDMNG", "VERID", "WRK01", "SERNR", "REVLV", "DBSKZ", "EXTNL", "ADINF", "RESLO", "PRIO_URG", "PRIO_REQ", "MPNMT", "GLCHANGETIME", "ZHDCLTIMESTAMP" FROM "SCO_PRD"."RAW_SAP"."MDTB";

Re: Possible Left Join ?

Clement15 — Fri, 13 Dec 2024 14:58:17 GMT

Hello,

When joining, Qlik will use all fields with common names as keys. In your code several fields between the two tables have common names. For example the fields "GLCHANGETIME",
"ZHDCLTIMESTAMP" should they really be used as keys?
If not you should think about renaming them to have different names between the tables.

Re: Possible Left Join ?

MikeGaunt1 — Fri, 13 Dec 2024 16:54:56 GMT

Hi,

I have renamed all the columns that match, and kept DTNUM.

The data loads but there is a lot of empty lines of data, that makes me think I did something wrong.

DTNUM from MDKP, then all the relevant fields from MDTB where the DTNUM matches.

I am wondering if I need to load MDKP first ?

Thanks

Re: Possible Left Join ?

Clement15 — Fri, 13 Dec 2024 18:12:29 GMT

Hello,

It depends on what you want to do. Maybe the documentation on the different joins could help you?

https://help.qlik.com/en-US/sense/May2024/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefixes/Join.htm?l=en-EN

topic Possible Left Join ? in App Development

Possible Left Join ?

Re: Possible Left Join ?

Re: Possible Left Join ?

Re: Possible Left Join ?