Comparison of values within a filed and with other field

[300sss300]

Hi,

I have two fields in a simple table  Invoice_No & Sale_Price (file attached). I want to add another column (Output in attached file) with REMARKS "SALE", "CANCEL" or "RETURN" based on following:

1. If Sale_Price is -ive (<0) & +ive where Invoice_No is same for both REMARKS column should show "CANCEL"

2. If Sale_Price is -ive (<0) and there are no +ive values for same invoice (i.e. Invoice is different).... REMARKS as "RETURN".

3. If Sale_Price is +ive (>0) and there are no -ive values for same invoice (i.e. Invoice is different).... REMARKS as "POS SALE".

Thanks

[MarcoWedel · Accepted solution]

Hi,

one solution might be:

tabSales:
LOAD Invoice_No,
     Sale_Price
FROM [D:\Daten\QlikView\QlikCommunity\Book1.xls] (biff, embedded labels, table is Sheet1$);

Join
LOAD Invoice_No,
     Pick(Match(Concat(DISTINCT Sign(Sale_Price)),'-11','-1','1'),'CANCEL','RETURN','SALE') as REMARKS
Resident tabSales
Group By Invoice_No;

hope this helps

regards

Marco

[300sss300]

Wow... It really worked.... Thank you so much.
Just for understanding Can you briefly explain below:

Join
LOAD Invoice_No,
Pick(Match(Concat(DISTINCT Sign(Sale_Price)),'-11','-1','1'),'CANCEL','RETURN','SALE') as REMARKS
Resident tabSales
Group By Invoice_No;

[MarcoWedel]

Hi,
glad you like it.
This code fragment adds the REMARKS column based on concatenated distinct sign values of Sale_Prices per Invoice_No.

Sign(Sale_Price)

evaluates to -1 for negative Sale_Prices and to 1 for positive ones.

So

Concat(DISTINCT Sign(Sale_Price))

delivers -1 for invoices that have only negative Sale_Prices, 1 if there are only positive Sale_Prices and -11 (i.e. -1 and 1 concatenated) for mixed Sale_Prices. You could use

Concat(DISTINCT Sign(Sale_Price),'/')

instead to better see this concatenation (resulting into '-1/1').

The Pick(Match()) combination then assigns CANCEL to -11 values, RETURN to -1 and SALE to 1.

hope this helps
regards
Marco

[300sss300]

Thanks Macro .

[300sss300]

Dear Marco,

Just noticed a little problem in "CANCEL"

The previous solution coding perfectly works for a scenario where a cashier scans items at POS machine and then For any reason whatsoever it is cancelled in that case Sale item lines (+ive values ) exactly matches with Return item lines (-ive values) for the same invoice number and in Remarks it is correctly showing "CANCEL"

scenario 2:

A customer brought 4 items at Cash terminal a cashier scans and then customer wants to cancel 3 and purchase only 1 item. In that case sale item lines will be 4 but return item line 3 and with this coding it is also showing cancel for the 1 item sold in the same invoice (please see attached files)

So I want to show 03 (+ive & -ive values) in same invoice as "CANCEL" but the only +ive value which doesn't have corresponding -ive value as "SALE" in same invoice.

Thanking you in advance.