topic Pivot Table Problem (not related data) in QlikView

Pivot Table Problem (not related data)

Wed, 25 May 2011 14:19:42 GMT

Hi everybody,

I have a problem to get the matching objects for the budget (data is not related by the date range):

Could somebody help me please to get the correct values in the column "Buget" for

- 01/2010 --> forecast-value "20" in column "Budget"

- 02/2010 --> forecast-value "231" in column "Budget"

Attached you can find an example QVW-File and the 3 example-XLS-Sheets.

Thanks a lot.

Regards,

jup

...

Pivot Table Problem (not related data)

Thu, 26 May 2011 09:33:01 GMT

hello juppinger,

i have the same problem an i'm interested of a solution too. Maybe you need here the "aggr" function, but i'm

not sure.

Does anyone have an idea ? Thanx..

Greetings,

Re: Pivot Table Problem (not related data)

kaushiknsolanki — Thu, 26 May 2011 09:43:54 GMT

Here is your Ans.

Have a look at the attched Document.

Regards,

Kaushik Solanki

Re: Pivot Table Problem (not related data)

Thu, 26 May 2011 11:51:39 GMT

Hi Kaushik,

very crazy - in my attached QVW-File I will not work!!!

Could you help me please again?

Thanks a lot,

jup

Re: Pivot Table Problem (not related data)

kaushiknsolanki — Thu, 26 May 2011 12:48:18 GMT

Hi,

Convert the month number in text as the field you are joining is a text.

try below.

text(num([HEL_DATA_Datum > MONTH]))

Regards,

Kaushik Solanki

Re: Pivot Table Problem (not related data)

Thu, 26 May 2011 13:19:53 GMT

Hey... other strange thing happens... (wrong values)

Please find the new qvw attached.

please help again

Re: Pivot Table Problem (not related data)

Fri, 27 May 2011 08:55:44 GMT

hi togehter,

really very strange ! I downloaded your file too and do not understand that. Exactly the same thing i want to do in my application and it also doesnt work.

If someone has a solution, i would be interested too. Thank you !

Kind regards

Re: Pivot Table Problem (not related data)

Mon, 30 May 2011 08:32:41 GMT

Please help us!

Thanks a lot,

jup

Re: Pivot Table Problem (not related data)

Mon, 30 May 2011 08:45:25 GMT

Hi,

Please also help. How do we get our best realized simplest problem?

Are there any recommendations? Thank you.

Best Regards

Re: Pivot Table Problem (not related data)

Tue, 31 May 2011 10:06:01 GMT

Found the matching topic...!

I also got this problem!!! 😞

could anyone help, please?

Re: Pivot Table Problem (not related data)

kaushiknsolanki — Tue, 31 May 2011 10:27:16 GMT

Hi,

I have found whats the problem is, please give me some time. I dont have my laptop with me now.

I will get back to you soon

Regards,

Kaushik Solanki

Re: Pivot Table Problem (not related data)

Tue, 31 May 2011 10:54:49 GMT

Hi Kaushik,

that would be great. Thank you very much !

Best regards,
Onur

Pivot Table Problem (not related data)

Tue, 31 May 2011 12:18:51 GMT

Well, it's crappy and doesn't scale well, but it works...

Instead of:

=forecast

use:

=Sum(If(month = month_forecast, forecast))

Here, the problem is that the expression "forecast" is translated by QV as "Only(forecast)" or "return me the forecast if there is ONLY ONE VALUE FOR DIMENSION COMBINATION. Actually you have 12, so you have to select only one of them. The one matching the month. I'm doing it with an If() function.

The Sum is just to have a total value. You could use Only() instead.

Good luck!

Pivot Table Problem (not related data)

Tue, 31 May 2011 12:58:39 GMT

Hi chiesa,

this is exactly what Kaushik wrote in his post/his example[1].qvw file.

But take a look at my example_Shrinked_FORUM.qvw file. In this file it doesn't work 😞

thanks,

jup

Re: Pivot Table Problem (not related data)

Tue, 31 May 2011 13:05:35 GMT

Whoops... I didn't read the topic until the end.

Well, in the last Qvw file you have this expression:

=sum(if(num([HEL_DATA_Datum > MONTH])= SAL_PLANDATEN_Budget,SAL_PLANDATEN_Budget))

This is wrong.

num([HEL_DATA_Datum > MONTH]) is a month (1-12).

SAL_PLANDATEN_Budget is a budget (dollars).

Using the if can't extract you anything.

You have to compare apples to apples .

Probably that line should read:

=sum(if([HEL_DATA_Datum > MONTH]=Num#(SAL_PLANDATEN_Monat), SAL_PLANDATEN_Budget))

If I understand the field names correctly.

Now, for the "wrong" values. It depends upon the many join you pass from table PLANDATEN (where the Budget is) to table HELIOS (where Month is). AFAICT, it makes no sense.

You should first understand what you're doing.

Probably, as a quick solution, you can do:

=Only(if([HEL_DATA_Datum > MONTH]=Num#(SAL_PLANDATEN_Monat), SAL_PLANDATEN_Budget))

Re: Pivot Table Problem (not related data)

Tue, 31 May 2011 14:58:02 GMT

Hello Chies080,

that's it! It was because of the num function. Many many thanks.

However, I did not quite understand the difference between num and num #. The help

in Qlikview is confusing. What I understand is that there probably is a difference between Intepretation and

formatting. Can someone explain to me in the very simple example of the difference. Thanks again

Best regards,

Pivot Table Problem (not related data)

Tue, 31 May 2011 15:25:59 GMT

Hey chiesa!

You did it! Great job - thanks very much and many thanks also to Kaushik 🙂

You are great!

jup

Pivot Table Problem (not related data)

Tue, 31 May 2011 17:36:36 GMT

The difference between Num and Num# is the difference between Formatting and Interpreting.

In the first case you have a value (numeric, usually - in QV this means a double) and you want to FORMAT the result. This means "value to string".

Interpreting means you have a string (a Text) and you want to convert it to a value (date, number, etc.).

So they do quite the opposite: Num() casts a number to string with formatting options, Num#() casts a string to a number using the provided options for string interpretation.

In the document provided by jup, if you create a table box with the month field in it, you can see the value is stored in QlikView as a string (this is basically wrong, AFAICT, and means an edit in the load script could be helpfull) from the alignment in the cell: the month is aligned to the left, as a text, instead on the right, as a number.

So, if you want to compare the month text to a number, you must convert it first to number using Num# (or, better, change the load script adding a conversion at load time). The same function Num# would do the job.

Good luck!

Re: Pivot Table Problem (not related data)

Wed, 01 Jun 2011 09:36:29 GMT

Hi chiesa80,

that explains everything. Thank you!

Best regards,