Hi All
I have a table loading that looks like the below : EXCEPT for Result this is the field in which I need to calculate on load.
So what I'd like is if there is any instance of TRAY = CJA and amount <>0 the all rows of data for application = SCC, and so on and forth.
I have the code and it will identify the instance of <>0 & CJA and Result = SCC but all other rows will be as per rest of code
Can anyone please advise ( this is related to this thread http://community.qlik.com/thread/99528 but I think I was asking wrong in the thread and misleading people)
Thanks
A
=IF(AND(D2>0,SUMPRODUCT(--($B$2:$B$25=B2),--($E$2:$E$25="CJACMP"))>0),"SCC",IF(AND(D2>0,SUMPRODUCT(--($B$2:$B$25=B2),--($E$2:$E$25="CJACMP"))=0),"Branch","Open"))
A B C D E F
Tray | Application | Status | Amount | Concatenate | Result |
CJA | 12311111 | CMP | 200 | CJACMP | SCC |
CJA | 12311111 | RDY | 200 | CJARDY | SCC |
Good | 12311111 | CMP | 200 | GoodCMP | SCC |
Post Finish | 12311111 | CMP | 200 | Post FinishCMP | SCC |
Review | 12311111 | CMP | 200 | ReviewCMP | SCC |
Shop | 12311111 | CMP | 200 | ShopCMP | SCC |
Shop | 12311111 | RDY | 200 | ShopRDY | SCC |
Start | 12311111 | CMP | 200 | StartCMP | SCC |
Start | 12311111 | RDY | 200 | StartRDY | SCC |
CJA | 22222222 | CMP | 0 | CJACMP | Open |
CJA | 22222222 | RDY | 0 | CJARDY | Open |
Good | 22222222 | CMP | 0 | GoodCMP | Open |
Post Finish | 22222222 | CMP | 0 | Post FinishCMP | Open |
Review | 22222222 | CMP | 0 | ReviewCMP | Open |
Shop | 22222222 | CMP | 0 | ShopCMP | Open |
Shop | 22222222 | RDY | 0 | ShopRDY | Open |
Start | 22222222 | CMP | 0 | StartCMP | Open |
Start | 22222222 | RDY | 0 | StartRDY | Open |
Good | 32122222 | CMP | 100 | GoodCMP | Branch |
Post Finish | 32122222 | CMP | 100 | Post FinishCMP | Branch |
Review | 32122222 | CMP | 100 | ReviewCMP | Branch |
Shop | 32122222 | CMP | 100 | ShopCMP | Branch |
Shop | 32122222 | RDY | 100 | ShopRDY | Branch |
Start | 32122222 | CMP | 100 | StartCMP | Branch |