Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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);

Tags (1)
1 Solution

Accepted Solutions
vijay_iitkgp
Valued Contributor

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

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

2 Replies
vijay_iitkgp
Valued Contributor

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

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

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

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

Thanks a million Vijay

Community Browser