straight table

[marikabi]

hello,

I'm working on a straight table and I really can't find a solution for my issue.

My data source looks like this:

Shop_A

Shop_Z

Quantity_shipped

eg

Shop_A	Shop_B	Quantity_shipped
Milan	Rome	10
Milan	Paris	50
Milan	Prague	8
Milan	Bratislava	20
Rome	Milan	5
Paris	Milan	15
Prague	Milan	10
Bratislava	Milan	18

What I would like to achieve is a table where for each shop combination A-B I see the quantity IN and the quantity OUT:

Shop_A	Shop_B	Quantity_shipped_IN	Quantity_shipped_OUT
Milan	Rome	10	5
Milan	Paris	50	15
Milan	Prague	8	10
Milan	Bratislava	20	18

Many thanks in advance

[sunny_talwar · Accepted solution]

May be try this

Table:
LOAD *,
	 RangeMaxString(Shop_A, Shop_B) as Shop_A_New,
	 RangeMinString(Shop_A, Shop_B) as Shop_B_New;
LOAD * INLINE [
    Shop_A, Shop_B, Quantity_shipped
    Milan, Rome, 10
    Milan, Paris, 50
    Milan, Prague, 8
    Milan, Bratislava, 20
    Rome, Milan, 5
    Paris, Milan, 15
    Prague, Milan, 10
    Bratislava, Milan, 18
];

and then a chart with these

Dimension

Shop_A_New
Shop_B_New

Expression

=Sum(If(Shop_A = Shop_A_New, Quantity_shipped))
=Sum(If(Shop_A <> Shop_A_New, Quantity_shipped))

[Sergey_Shuklin]

Hello!

This way will give you exactly expected output. But there is one condition: repeated values in field "Shop_A" (such as Milan) will be marked as target values and will be shown in the result table.

[marikabi]

Dear Sunny,

thanks for your help!

I created the dimensions directly in the chart and it seems working quite well.

The only issue I have is that Shop_A and Shop_B are identified by codes, and translated thanks to a mapping table (the format for A and B is different) - for the values of Shop_A and Shop_B not yet included in the mapping table, I loose the amounts in the chart even if selected to include zero values and missing amounts.

Any idea how to solve this?

[sunny_talwar]

Would you be able to share a sample of the issue you have just mentioned? Might be easy to see and play around if we have a sample to see what you have implemented

[marikabi]

Hi Sunny,

as you can imagine I cannot upload real data and to fake them it would take hours.

Just to make an example

Shop_A Milan is identified by codes Shop_A_code: 123 or 132 or 567 or 945

Shop_B_Milan is identified by codes Shop_B_code:  0778 or 0698 or 0736 or 04882

-> there is no correlation between A and B codes even for the same shop

Also, it can happen that the shipment is made from Milan to Milan

Now, to translate Shop_A_code and Shop_B_code (which are in my data source) to shops, I have an excel file With all codes and associated shops.

I'm filling in this table manually every time I discover a new shop (unfortunately this is documented in word docs, we don't have this info in any database), and for the combinations where Shop_A is not filled in neither Shop_B is, Qlik return 0 IN and 0 OUT

[sunny_talwar]

"for the combinations where Shop_A is not filled in neither Shop_B is, Qlik return 0 IN and 0 OUT"

So, what do you expect to see here, if not 0?

Also, from your post it seems that you have the mapping all sorted out, right? or is that what you think is causing issues?

[marikabi]

unfortunately, I don't have all the shops mapped for all the codes - so I have some quantity shipped for missing shops, and I would expect to have

Shop_A : -

Shop_B: -

quantity in : x amount

quantity out : y amount

[sunny_talwar]

Oh so, you are looking for a Null dimension with in and out... Did you follow the approach that I provided you or did you use another method to display in and out?

[marikabi]

I used your approach but defining Shop_A_New and Shop_B_New a dimension in the chart and not in the loading script