Inner Join with condition

[#1]

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

[Gysbert_Wassenaar · Accepted solution]

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...

[#3]

Thanks Gysbert Wassenaar for the answer

[#4]

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;

[sunny_talwar]

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;