Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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);
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
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
I forget that you can manipulate the resident tables by just loading them again.
Thanks a million Vijay