Create a table with data from 2 tables

[#1]

Hello, i need help with create a 3rd table with data from 2 tables:

One of my tables contains 2 columns: client_id, description, the other table contains the sales detail: client_id, date, qty, price

I need to create a third table.

In sql i would do something like: select a.client_id, a.description,  sum(b.qty) as qty_total, sum(b.qty * b.price) as value_total from clients a, orders b where a.client_id =b.client_id group by a.client_id, a.description order by a.client_id, a.description

Can i do something like this in qlikview?

TIA

Joaquim

[#2]

you can create a new 3rth table using the resident, I don´t know if you mean this but if you plase an example it would be better to create an script in your app.

Greetings!!

[#3]

Hi

You don't need to merge your 2 tables . It's the philosophy of Qlikview working with linked fields.

So if you want to display the total sales by client, just create a chart (straight table) with

dimension =  client_id

expression  =  sum(  qty * price ) 

However, if you want to merge :

table1:

Load client_id,  description from you datasource1 ;

left join(table1)

Load client_id, qty, price, data 

from your datasource2;

Hope that helps

JJ

[#4]

Thank you Jean-Jacques for the quick answer.

Yes, i'm aware of the QlikView philisophy.

And i know we have much to gain with that phylosophy, the problem is that some calculations needs so much knowledge of the software that it isn't pratical to do it when we are shortage of time and we don't have the expertise to do that the right way.

My problem is that i need to build a chart that compare each line of the detail (or aggregated values of the detail) with the total amount for the exact month of that line.

So, i have 1000 lines of detail for the month 1, 1000 lines of detail for the month 2, ...

One of the columns has quantities and other has prices, say we have 1000€ total for the month 1, 1000€ for the month 2, ...

My chart needs to display for some categories of data, the % of each month respectively.

The easiest way to do that is to create a temp table (or what you call it) with the following columns: year, mounth, total_amount_for_the_mounth, than i can use that value (total_amount) in the chart calculations to find the %, QlikView will be responsable for linking the values with the right detail lines.

If you know a better (and easy) way to do that in QlikView i will be thankful to you if you let me know.

Thank you again.

Joaquim

[#5]

Thank you for the quick answer Javier.

I don't have the data with me (it's at my job), so i can't send you the example.

I have a file that collects data from some qvd files.

I need to create a, let's call it, temporary table, that holds some key values as well as a calculated field.

And i need to do it after i get all the data from the qvd's.

Maybe a script that is run after all the data is read.

All i need is a sample of a script (with dummy data) and the way to call it after all the data is read.

Thank you again.

Joaquim

[#6]

Ok, then it would be something like this:

Table1:

Field A,

Field B,

Field C

From, file.xls.

Table2:

Field A,

Field D,

Field E

From file.xls

Temporary Table1:

Field A,

Field C

Resident Table1;

Concatenate (Table1)

Temporary Table2:

Field D,

Field E

Resident Table2;

then you will get a new temporary Table1.

I hope is clear enough!!

[#7]

Thank you again Javier.

We are getting closer, but my problem is to create a new calculated field from the aggregation of an existing field.

Taking your example it would be something like:

Table1:

Field A,

Field B,

Field C

From, file.xls.

Temporary TableTemp:

Field A,

Field B,

Sum(Field C)

From Resident Table1;

Group by field A, Field B

Can i do something like that?

Cumprimentos,

Joaquim Correia

Sistemas de Informação

Tel.: (351) 220 403 243 / 00 • Fax: (351) 229 022 107

Tlm.:(+351) 919 112 623 • e-mail: joaquim.correia@b2b-pt.com<mailto:luis.ferreira@b2b-pt.com>

De: javierortiz

Enviada: terça-feira, 10 de Janeiro de 2012 21:18

Para: B2B-Joaquim Correia (Informática)

Assunto: - Re: Create a table with data from 2 tables

QlikCommunity<http://community.qlik.com/index.jspa>

Re: Create a table with data from 2 tables

created by javierortiz<http://community.qlik.com/people/javierortiz> in Development (QlikView Desktop) - View the full discussion<http://community.qlik.com/message/181469#181469

[vijay_iitkgp · Accepted solution]

Hi Joaquim,

Definitely you can do this.

You can do

Table2:

Load

[Field A],

[Field B],

Sum(Field C) AS [Field C]

Resident Table1 Group By [Field A], [Field B];

After this you can drop the previous table.

[#9]

Thank you Vijay and thank you all.

I tested it and it worked.

Cumprimentos,

Joaquim Correia

Sistemas de Informação

Tel.: (351) 220 403 243 / 00 • Fax: (351) 229 022 107

Tlm.:(+351) 919 112 623 • e-mail: joaquim.correia@b2b-pt.com<mailto:luis.ferreira@b2b-pt.com>

De: Vijay Singh

Enviada: quarta-feira, 11 de Janeiro de 2012 08:29

Para: B2B-Joaquim Correia (Informática)

Assunto: - Re: Create a table with data from 2 tables

QlikCommunity<http://community.qlik.com/index.jspa>

Re: Create a table with data from 2 tables

created by Vijay Singh<http://community.qlik.com/people/vijay_iitkgp> in Development (QlikView Desktop) - View the full discussion<http://community.qlik.com/message/181549#181549

[#10]

Sorry, but i have one more question that links with my previous problem:

If the data come from 2 tables there is a way to relate them?

Example:

Table1:

Load

, ,

From file1;

Table2:

Load

,

From file2;

TempTable:

Load

a.[FieldA], a.[FieldB], b.[FieldD], sum(a.[FieldC]) as FieldX

resident Table1 a, resident Table2 b

where a.[FieldA] = b.[FieldA]

group by a.[FieldA], a.[FieldB], b.[FieldD]

order by a.[FieldA], a.[FieldB], b.[FieldD]

Cumprimentos,

Joaquim Correia

Sistemas de Informação

Tel.: (351) 220 403 243 / 00 • Fax: (351) 229 022 107

Tlm.:(+351) 919 112 623 • e-mail: joaquim.correia@b2b-pt.com<mailto:luis.ferreira@b2b-pt.com>

De: Vijay Singh

Enviada: quarta-feira, 11 de Janeiro de 2012 08:29

Para: B2B-Joaquim Correia (Informática)

Assunto: - Re: Create a table with data from 2 tables

QlikCommunity<http://community.qlik.com/index.jspa>

Re: Create a table with data from 2 tables

created by Vijay Singh<http://community.qlik.com/people/vijay_iitkgp> in Development (QlikView Desktop) - View the full discussion<http://community.qlik.com/message/181549#181549