topic Re: How to turn a dimension in the same table? in QlikView

How to turn a dimension in the same table?

Mon, 27 Apr 2015 07:30:39 GMT

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

Re: How to turn a dimension in the same table?

ankitaag — Mon, 27 Apr 2015 07:53:56 GMT

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

Re: How to turn a dimension in the same table?

awhitfield — Mon, 27 Apr 2015 08:56:12 GMT

Hi Tom,

can you post your qvw?

Regards

Andy

Re: How to turn a dimension in the same table?

Mon, 27 Apr 2015 10:41:33 GMT

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

Re: How to turn a dimension in the same table?

ankitaag — Mon, 27 Apr 2015 11:29:54 GMT

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)

Re: How to turn a dimension in the same table?

ankitaag — Mon, 27 Apr 2015 11:37:24 GMT

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

Re: How to turn a dimension in the same table?

Mon, 27 Apr 2015 11:40:54 GMT

Hi Ankita,

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

Re: How to turn a dimension in the same table?

ankitaag — Mon, 27 Apr 2015 11:49:25 GMT

Hi Tom,

Can you please look over the attached app..

I have made some changes in the script

Re: How to turn a dimension in the same table?

Wed, 29 Apr 2015 07:07:21 GMT

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

Re: How to turn a dimension in the same table?

ankitaag — Wed, 29 Apr 2015 08:58:04 GMT

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?

Re: How to turn a dimension in the same table?

Wed, 29 Apr 2015 09:38:32 GMT

From destination	To destination	Tons
Oslo	London	20000
London	Oslo	10000

This is what the end result should look like:

I have on dimension for Tons, From and To. When you choose From, the amount of tons sendt to the To destination will be showed. I need to turn this this, sow the T"To destination" will be the same as the "From destination". That way I will get the retured tons on the same transportation line.

From Oslo to London 20 000 tons - Returned (London - Oslo) 10 000 tons.

The problem is to get QV to understand that the calculations of the returned (10 000) need to pick the "To Destination" and use it as a "From Destination"....

That's why i tried:

From Oslo to London: Sum(tons) - From London to Oslo: Sum({$<[From Destination] = {[To Destination]}>} Tons)

///Tom

Re: How to turn a dimension in the same table?

ankitaag — Wed, 29 Apr 2015 10:17:43 GMT

Hi Tom,

Finally !! so glad to do this.

Kindly find the attached app.

Final result is exactly what you want.

Hope this is correct and helpful!

Thanks and Regards,

Ankita

Re: How to turn a dimension in the same table?

Wed, 29 Apr 2015 10:27:23 GMT

Hi,

This is exactly what I need! I'm very impressed!!

Thank you!!!

This solve my problem.

///Tom

Re: How to turn a dimension in the same table?

ankitaag — Wed, 29 Apr 2015 10:29:20 GMT

Thankyou so much