How to turn a dimension in the same table?

[#1]

Hi,

I've encountered a problem I can not solve and need some help!

I have the dimensions "From Destination" and "To Destination". I use this to calculate the number of tons between differents locations, with the expression sum(tons). But, I need to turn this expression to how many tons who are sent back along the same line in return.

In other words, I must fetch up the "To Destination" and make it calculate this as an "From Destination". I tried to use SET expression like Sum({$<[From Destination] =  {[To Destination]}>} Tons), but it returns only zero...

Is there anyone who can help with some suggestions here?

///Tom

[ankitaag]

Hi Tom,

Qlikview doesn't allow to column names to be same.

So you need to change the 2nd From and To Destination to From Destination 1 and To Destination 1 and Tons to be Tons Returned.

That way it will be easy for you to write an expression.

Because I suppose you need extra column for Return from London to Oslo as shown in your image

[awhitfield]

Hi Tom,

can you post your qvw?

Regards

Andy

[#4]

Hi Andrew,

This is just a test file since the original is too big and not allowed to publish (company rule).

Hope this helps describing my problem...

///Tom

[ankitaag]

Hi Tom,

You can store the From Destination value in vTo variable (Oslo) and ToDestination value in vFrom variable (London).

and in the expression you can write

=sum({<[From Destination]={'$(vTo)'},[To Destination]={'$(vFrom)'}>}Tons)

[ankitaag]

Do you have just 1 from and To location or you have many?

[#7]

Hi Ankita,

No, I have over 300....
///Tom

[ankitaag]

Hi Tom,

Can you please look over the attached app..

I have made some changes in the script

[#9]

Hi Ankita,

Thank you for responding and for the changes you did in the QV! This was good input and a new approach!

However, I want the returned tonnes in the same line as in the example picture that is attached.

Anyway, thanks responding! I really appreciate that!

///Tom

[ankitaag]

In your data table you have a separate row for the returns right?

Cant we bring them to the same row by creating extra columns?