Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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;
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
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.