Compare values between QVDs in Pivot table.

[#1]

Hello people,

I would like some help from you.

I have more than 2 qvd files (qvd1 and qvd2) as follows:

I want to create a pivot table like this:

But I want to use this formulas:

AMOUNT:

if(qvd1.detail = qvd2.detail and qvd1.year = qvd2.year and qvd1.month = qvd2.month, sum(qvd1.amount), 0)

BUDGET:

if(qvd1.detail = qvd2.detail and qvd1.year = qvd2.year and qvd1.month = qvd2.month, sum(qvd1.budget), 0)

%:

(column(3)-column(1))/column(1)

The problems is: WHEN I APPLY THOSE FORMULAS (AMOUNT and BUDGET) the pivot table does not show any data, like it would have not data, but there is data.
To get qv1.*, qvd2.*, ... I am using qualify and all my big project was developed usign qualify.

Any recomendation please.

When I get a table with qvd1.detail and qvd2.detail it making a cross join:

[sunny_talwar]

Are you using Qualify statement to bring them as two separate tables? If not, then you wouldn't need to use any if statement and it should join in the backend to show proper results on the front end.

[#3]

Yes, I am using qualify and unqualify, because I am working with so much data. I have more than 20 QVD files. I don´t use concatenate because I would get a QVD so big and so a slow Dashboard.

[sunny_talwar · Accepted solution]

Using the if statement is the worst possible option in case of large data set. This blog here explains two ways to handle data set where 2 or more fields are common between tables: Concatenate vs Link Table. Personally, I think that Concatenation will give the best performance on the front end because the data is all in one table vs. Link Table which will load faster because it doesn't have to do the extra processing of putting everything together in one table on the back end (but will have to do some lifting on the front end to compensate for it)

[santiago_respane]

Hi Diego,

totally agree with Sunny, if i where you i'd join both tables by DETAIL, YEAR and MONTH.

This way:

-you wont need any strange formulas containing if clauses

-your data will be well linked

-your UI will run faster

Hope this helps.

Kind regards,

[rwunderlich]

Concatenating these into a single table in the dashboard model should be much faster.

-Rob

[swuehl]

Could you describe a bit more detailed what you want to achieve with something like

AMOUNT:

if(qvd1.detail = qvd2.detail and qvd1.year = qvd2.year and qvd1.month = qvd2.month, sum(qvd1.amount), 0)

It seems you want to compare detail, year and month in both tables (on a per record base? Only checking if the values of one table exist also in the other?).

But then you want to return only aggregated amount of qvd1?

If you only want to check if the combination of dimensional values of the first QVD exist also in the other QVD, you may want to create a table with the combined values of the one QVD, then use EXISTS(KeyMadeOfQVD1Values, KeyMadeofQVD2Values) function in the load script to flag your amount values.

[#8]

Hello stalwar1‌,

I know it's a better way to use CONCATENATE but I am having problems reading concatenated files.

Steps:

1. I have a load.qvw to create qvd files that finally will be read by a dashboard.qvw.

2. In the load.qvw I use CONCATENATE with to files: real.qvd and presupuesto.qvd. Those files have the almost the same number of columns and most of them have the same number. So I generate a final.qvd.

3. Using dashboard.qvw, I load final.qvd and other qvd files, but when I create a table, data from presupuesto.qvd are not loaded correctly. I don't use "where" statement.

As you can see, year and month are not there, but I don't know why.

4. Here something weird. If I create a dashboard2.qvw and I load the final.qvd, I have all data. I dont use dashboard2.qvw because dashboard.qvw is a almost finished project.

It´s important to mention that all rows have year and month and I use them to filter data into a Pivot table.