2 Replies Latest reply: Jan 11, 2012 1:38 AM by smoothjadesa RSS

    create a new field based on the result of an outer join?

      Hi,

       

      I'm new to qlikview and have the following problem:

       

      I've got 2 tables. Orders and Payments.

      I use an Outer Join to join the two tables. Orders that have been paid will match with a line in the Payments Table.

      Orders that havent been paid obviosuly won't match and will have null values for all the Payment fields that get joined.

       

      I want to create a paid flag as a list box based on that.

       

      Here is an extract of my script:

       

      [C2 LOTDETS]:

      LOAD lotnum as [C2 Lotnum],

           dp as [C2 dp],

           dept as [C2 dept],

           lotdetid as [C2 lotdetid],

           clotdetid as [C2 clotdetid],

           xlotdetid

      FROM

      C:\qvdextract\BR\LOTDET.qvd

      (qvd)

      WHERE dept <> 127;

       

       

      INNER JOIN([C2 LOTDETS])

      LOAD lotdetid as [C2 lotdetid],

           palstkid as [C2 palstkid]

      FROM

      C:\qvdextract\BR\PALSTK.qvd

      (qvd);

       

       

      [C2 LOTHEDS]:

      LOAD lotnum as [C2 Lotnum],

           ponum,

           type,

           APPLYMAP('LOT STATUS',status) as [Lot Status],

           APPLYMAP('SUPPLIERS',supcode) as Supplier,

       

           acsnum,

           supref,

           comment,

           invdate

      FROM

      C:\qvdextract\BR\LOTHED.qvd

      (qvd)

      WHERE supcode<>'ZZZZZ';

       

       

      JOIN([C2 LOTHEDS])

      LOAD date(postingdate) as [Posting Date],

           invnum as acsnum,

           postref as [Posting Ref],

           val as [Payment Val]

      FROM

      C:\qvdextract\BR\PURFIL.qvd

      (qvd);