Consolidate 2 rows with different structure

[Rob0]

Hello!

I have the following data:

The challenges:

1) Cost A has a validate value for every article. So I need the sum for every ticket. For ticket 4325 it is 10+5=15

2) If cost B is 0, Cost A should be considered.

End result should be a chart that shows the total costs for every queue:

A: 10+5=15

B: 5

C: 15

Thanks for help! 

[sidhiq91]

@Rob0  I can help you out to resolve this issue, but I am not clear with your requirement.

For Example, for Ticket 2434 should the cost not be 10+20=30

Please confirm on this first.

[Rob0]

Hi @sidhiq91 

No, for Ticket 2434 it should be 10, because it should always prefer Cost B. Only if Cost B is 0, it should consider Cost A.

I guess I can work with something like:

2 new columns:

The first one just takes the value from Cost B, but distinct (only one value for one ticket#)

The second one takes the values from the first one, if it's not 0. If that's the case, it takes the values from Cost A. 

I'm sorry, I'm new to Qlik and I also have to learn how to describe my problem in a better way 😕

[tealowk]

Hi @Rob0 ,

I have reproduced your table using the available transformations in the load script.

It works as long as the same ticket numbers appear subsequently in the data set. If you're not sure about that, we can add a ORDER BY clause in the resident load.

Here is the script I used:

Data:
LOAD * INLINE [
Article, Ticket, Queue, Cost A, Cost B
001, 2434, A, 10, 10
002, 2434, A, 20, 10
003, 1234, B, 5, 5
004, 5467, A, 5, 5
005, 5467, A, 10, 5
006, 5467, A, 5, 5
007, 4325, C, 10, 0
008, 4325, C, 5, 0
];

AddCost:
LOAD
     *,
     IF([Cost B] > 0, IF(PREVIOUS(Ticket) = Ticket, NULL(), [Cost B]), [Cost A]) AS COST
RESIDENT Data;
DROP TABLE Data;

[sidhiq91]

@Rob0  as @tealowk mentioned the solution is pretty simple. Please see the code below:

NoConcatenate
Temp:
Load * Inline [
Article,Ticket,Queue,Cost A,Cost B,
001,2434,A,10,10
002,2434,A,20,10
003,1234,B,5,5
004,5467,A,5,5
005,5467,A,10,5
006,5467,A,5,5
007,4325,C,10,0
008,4325,C,5,0
];

NoConcatenate
Temp1:
Load Article,Ticket,Queue,[Cost A],[Cost B],
If([Cost B] > 0, IF(PREVIOUS(Ticket) = Ticket, NULL(), [Cost B]), [Cost A]) AS COST

Resident Temp
Order by Article;

Drop table Temp;

Exit Script;

If this resolves your issue, please like and accept it as a solution.