topic Inner Join with condition in QlikView

Inner Join with condition

Wed, 25 Nov 2020 16:16:04 GMT

Dear Everyone,

I am new in this Qlikview and I and trying to use inner join to combine two tables.

Table A involves Sum(Distinct) to workout the sum of an field X from SAP Table EKBZ where some of the such sum will be zero..

Table B will load the rest of fields from EKBZ for those record where the sum of the distinct field X in the Table A.

TableA:

Load

Sum(Distinct (if([SHKZG] = 'H', [DMBTR], [AREWR]*(-1)))) as Deli_Amt_Process,

EBELN & ' ' & EBELP & ' ' & STUNR & LIFNR as Deli_GRIR_Key

FROM EKBZ

(qvd)

GROUP By EBELN, EBELP, STUNR, LIFNR;

inner Join

TableB:

Load

EBELN & ' ' & EBELP & ' ' & STUNR & LIFNR as Deli_GRIR_Key,

BELNR as Deli_Vendor_Invoice,

BELNR as Deli_Last_SES_No,

BUDAT as Deli_Last_Postng_Date,

WRBTR as Deli_GR_Value_Currency,

WAERS as Deli_GR_Value_Currency_Type,

DMBTR as Deli_GR_Value,

LIFNR as Deli_Vendor_No,

KSCHL as Deli_Condition_Type,

EBELN as PO_No,

EBELP as PO_Item,

STUNR as PO_Step

FROM EKBZ

(qvd);

However, I do not want those Deli_Amt_Process with zero to be joined. Is there a way to have conditional inner join?

Thanks

Re: Inner Join with condition

Gysbert_Wassenaar — Fri, 22 Jul 2016 08:03:55 GMT

Use a Preceding Load:

TableA:

LOAD If(Deli_Amt_Process = 0 , '__String_To_Prevent_Join_Here__', Deli_GRIR_Key ) as Deli_GRIR_Key ;

Load

Sum(Distinct (if([SHKZG] = 'H', [DMBTR], [AREWR]*(-1)))) as Deli_Amt_Process,

EBELN & ' ' & EBELP & ' ' & STUNR & LIFNR as Deli_GRIR_Key

FROM EKBZ

(qvd)

GROUP By EBELN, EBELP, STUNR, LIFNR;

etc...

Re: Inner Join with condition

Fri, 22 Jul 2016 09:02:28 GMT

Thanks Gysbert Wassenaar for the answer

Re: Inner Join with condition

Fri, 29 Jul 2016 02:23:04 GMT

Hi Everyone,

The Preceding load method by Gysbert Wassenaar works and meet the objective but somehow I can't get the Deli_Amt_Process display or store for other usage. Is there something I can improve on?

Thanks!

LOAD If(Deli_Amt_Process = 0 , '__String_To_Prevent_Join_Here__', Deli_GRIR_Key ) as Deli_GRIR_Key;

Load

Sum(Distinct (if([SHKZG] = 'H', [DMBTR], [AREWR]*(-1)))) as Deli_Amt_Process,

EBELN & ' ' & EBELP & ' ' & STUNR & LIFNR as Deli_GRIR_Key

FROM EKBZ

(qvd)

GROUP By EBELN, EBELP, STUNR, LIFNR;

Re: Inner Join with condition

sunny_talwar — Fri, 29 Jul 2016 04:51:19 GMT

May be this:

LOAD Deli_Amt_Process,

Deli_GRIR_Key

Where Deli_Amt_Process <> 0;

Load

Sum(Distinct (if([SHKZG] = 'H', [DMBTR], [AREWR]*(-1)))) as Deli_Amt_Process,

EBELN & ' ' & EBELP & ' ' & STUNR & LIFNR as Deli_GRIR_Key

FROM EKBZ

(qvd)

GROUP BY EBELN, EBELP, STUNR, LIFNR;