Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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);

1 Solution

Accepted Solutions
vijay_iitkgp
Partner - Specialist
Partner - Specialist

Hi,

First create the replica  of Key field in both table and after joining load it resident and create a flag as if(Order_Key=Payment_Key,'Paid','Unpaid') as Flag.

Eg:

Table:

Key,

Key As Order_Key,

A,B

From Order.qvd

Join

Key,

Key AS Payment_Key,

X,Y

From Payment.qvd;

Table1:

Noconcatenate

Laod

*,

If(Order_Key=Payment_Key,'Paid','Unpaid') as Flag

Resident Table;

Drop Table Table;

Drop Field Order_Key,Payment_Key;

Hope this will help

View solution in original post

2 Replies
vijay_iitkgp
Partner - Specialist
Partner - Specialist

Hi,

First create the replica  of Key field in both table and after joining load it resident and create a flag as if(Order_Key=Payment_Key,'Paid','Unpaid') as Flag.

Eg:

Table:

Key,

Key As Order_Key,

A,B

From Order.qvd

Join

Key,

Key AS Payment_Key,

X,Y

From Payment.qvd;

Table1:

Noconcatenate

Laod

*,

If(Order_Key=Payment_Key,'Paid','Unpaid') as Flag

Resident Table;

Drop Table Table;

Drop Field Order_Key,Payment_Key;

Hope this will help

Not applicable
Author

I forget that you can manipulate the resident tables by just loading them again.

Thanks a million Vijay