topic Subquery on a Script in QlikView

Subquery on a Script

Mon, 30 Aug 2010 23:22:17 GMT

Hello Experts,

Is it possible create a subquery like this example?

I need aggregate data of two or more tables. Tks

create table Test

from ( table1:

load column1, column2

from Text_file1;

add load column1, column2

from textfile2)

Subquery on a Script

Oleg_Troyansky — Mon, 30 Aug 2010 23:33:41 GMT

Alexandre,

Two QlikView LOAD statements will do just what you need - CONCATENATE or append the data from one table to another. You don't need to use any CREATE TABLE... Here is an example:

Tab3:

load C1, C2 from ...;

While your fields are exactly the same, the Concatenation will happen automatically. If the fields are slightly different, use prefix CONCATENATE for your subsequent loads.

good luck,

Subquery on a Script

Mon, 30 Aug 2010 23:38:46 GMT

Ok,

But how Do I do to aggregate this data on other table?

Subquery on a Script

stevedark — Tue, 31 Aug 2010 00:31:24 GMT

HI Alexandre,

Once the tables are loaded and concatenated as Oleg describes any expressions you write in a chart will aggregate data across both of the source tables.

If you wish to aggregate the data on the way in then you can use SUM statements in the load script and do a LOAD DISTINCT or GROUP BY. The syntax for GROUP BY needs to be precise - so it is worth referring to the help file.

Hope that helps,

Regards,
Steve

Subquery on a Script

Tue, 31 Aug 2010 20:25:30 GMT

But I have two loads in a same table firt Day-1 and after Day each one houer, and I need the data keep aggregated by customer by example.

on a first load

Client |sale

AAAA |50000

after the second Load

Client |sale

AAAA |50000

AAAA |30000

The problem is I have other table BUDGET with:

Client | Budget

AAAAA | 900000

When QlikView make the relationship the data keep Worng

Client | Sale | Budget

AAAA | 50000 |900000

AAAA | 30000 |900000

And my Budget keeps Duplicated

You Got me?

Subquery on a Script

Tue, 31 Aug 2010 20:49:03 GMT

Actually is not duplicated.....just show two times because has a two reference!!!

Subquery on a Script

Tue, 31 Aug 2010 20:59:16 GMT

Ok but my dinamic table show me

Client | Sale | Budget

AAAA | 80000 | 1800000

And the correct must be

Client | Sale | Budget

AAAA | 80000 | 900000

But for this I have to aggreg my data in LOAD time By Client

Right?

Subquery on a Script

Tue, 31 Aug 2010 21:06:10 GMT

You need put all sales on the same table.....and in another table you put a budget.....and two tables must has a Client like a key??

Did you do that?

Subquery on a Script

Tue, 31 Aug 2010 21:17:37 GMT

My data are refreshed every 15 minutes, so I do 2 Loads the first with month data and the refresh is only daily datas.

I have other table with budget. like you sad with clint like Key

Subquery on a Script

Tue, 31 Aug 2010 21:22:55 GMT

Fisrt you put on QVD.....and second you concatenate with QVD on the same table?

Subquery on a Script

Tue, 31 Aug 2010 21:34:08 GMT

Yes!! correct!

Subquery on a Script

stevedark — Tue, 31 Aug 2010 22:30:53 GMT

Hi there,

You do not need to be concatenating or joining the tables at all in the load script. In fact you need to be sure that you don't. The two tables should be loaded separately and will be joined in the data model by virtue of the fact that the field Client exists in both tables.

Check the table viewer after a reload to check that this is happening.

Once the data is in create a chart with a dimension of Client and two expressions SUM(Sale) and SUM(Budget). You can select to show your chart as a 'Straight Table'. This should give you the results that you are after.

If you add the values in a Table Box then the numbers will not be aggregated and you will get the scenario where you see the two separate values for Sale and the value for Budget shown twice. It is rare that you will ever want to put a numeric value in a Table Box - it is almost always preferable to use a Chart with a Chart Type of straight table.

I can post up an example of this working if you require.

Regards,
Steve

Subquery on a Script

Tue, 31 Aug 2010 22:55:14 GMT

Could You post this example?

Subquery on a Script

Wed, 01 Sep 2010 00:02:22 GMT

Tks Steve! It is ok!!

QlikView is the best BI tool!!!!

Subquery on a Script

stevedark — Wed, 01 Sep 2010 07:46:15 GMT

Hi Alexandre,

Glad it is now working for you.

Regards,
Steve