Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Qlik members,
I got a question concerning flag.
I need to apply a flag for invoices when i found a certain combination of products code. The Flag need to be apply only on combination of Invoice Number & product code
For example need to put a "PROMO Flag" if i found codes 4,5 and 7. Below Invoice DB structure :
Any idea if it's easier to do through Expresssion or Script ? (I remember a possible solution trough Possible P() expression with set analysis, but no insurance with the syntax.
Thanks a lot
Invoice | Product | Flag |
A | 1 | |
A | 4 | Promo |
A | 5 | Promo |
A | 7 | Promo |
A | 8 | |
B | 2 | |
B | 3 | |
B | 1 | |
B | 5 | |
C | 4 | |
C | 5 | |
C | 6 | |
C | 9 | |
C | 10 | |
D | 1 | |
D | 2 | |
D | 4 | Promo |
D | 5 | Promo |
D | 7 | Promo |
D | 9 |
An expression based solution:
Dimension
Invoice
Product
Expression
Only({<Invoice = p({<Product = {'4', '5', '7'}>}), Product = {'4', '5', '7'}>} 'Promo')
Try this
Table:
LOAD * INLINE [
Invoice, Product
A, 1
A, 4
A, 5
A, 7
A, 8
B, 2
B, 3
B, 1
B, 5
C, 4
C, 5
C, 6
C, 9
C, 10
D, 1
D, 2
D, 4
D, 5
D, 7
D, 9
];
Temp:
LOAD Invoice,
If(Count(Product) = 3, 'Promo') as Flag
Resident Table
Where Match(Product, 4, 5, 7)
Group By Invoice;
Left Join (Temp)
LOAD Distinct Product
Resident Table
Where Match(Product, 4, 5, 7);
Left Join (Table)
LOAD *
Resident Temp;
Expression based
Dimension
Invoice
Product
Expression
If(Count(DISTINCT TOTAL <Invoice> {<Product = {4, 5, 7}>} Product) = 3 and Match(Product, 4, 5, 7), 'Promo')