topic Re: Create a table with data from 2 tables in QlikView

Create a table with data from 2 tables

Tue, 10 Jan 2012 18:59:03 GMT

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

Create a table with data from 2 tables

Tue, 10 Jan 2012 19:11:47 GMT

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!!

Create a table with data from 2 tables

Tue, 10 Jan 2012 19:23:58 GMT

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

Re: Create a table with data from 2 tables

Tue, 10 Jan 2012 20:48:42 GMT

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

Re: Create a table with data from 2 tables

Tue, 10 Jan 2012 20:54:42 GMT

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

Create a table with data from 2 tables

Tue, 10 Jan 2012 21:18:15 GMT

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!!

Re: Create a table with data from 2 tables

Wed, 11 Jan 2012 08:20:44 GMT

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

Create a table with data from 2 tables

vijay_iitkgp — Wed, 11 Jan 2012 08:29:20 GMT

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.

Re: Create a table with data from 2 tables

Wed, 11 Jan 2012 08:33:57 GMT

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

Re: Create a table with data from 2 tables

Wed, 11 Jan 2012 09:01:36 GMT

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

Re: Create a table with data from 2 tables

Wed, 11 Jan 2012 09:09:24 GMT

Sorry again, i don’t know what happened with the previous code, so i send the message again with the code part corrected.

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

FieldA, FieldB, FieldC

From file1;

Table2:

Load

FieldA, FieldD

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]

Joaquim

Re: Create a table with data from 2 tables

vijay_iitkgp — Wed, 11 Jan 2012 09:18:06 GMT

Hi,

You can first Join two tables like:

Table1:

Load

[Field A],

[Field B],

From File1

Join (If you want Inner Join then use Inner Join)

Load

[Field A],

[Field C]

From File2;

Table2:

Noconcatenate

Load

[Field A],

[Field B],

Sum([Field C]) As [Field C]

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

Drop Table1;

Re: Create a table with data from 2 tables

Wed, 11 Jan 2012 09:47:15 GMT

Thank you again Vijay.

I tried but i got a syntax error.

CENTROABASTECEDOR:

LOAD ,

FROM T001W.QVD (qvd);

VENDASBW:

LOAD ,

OverPrice,

Qtd.vendas,

Vendas,

Mid(,14,18) as

FROM

(qvd)

join

LOAD

FROM T001W.QVD (qvd);

I got the message:

In english would be something like “Unrecogizable words (garbage) after command:”

Joaquim

Re: Create a table with data from 2 tables

vijay_iitkgp — Wed, 11 Jan 2012 10:05:53 GMT

Sorry my Mistake... Remove Comma from Last Field ([Field B]) in Table 1 Table1: Load [Field A], [Field B] From File1 Join (If you want Inner Join then use Inner Join) Load [Field A], [Field C] From File2;

Re: Create a table with data from 2 tables

Wed, 11 Jan 2012 10:09:29 GMT

Thank you Vijay.

But that is not the problem, because, as you see, there is no coma after the last field:

VENDASBW:

LOAD ,

OverPrice,

Qtd.vendas,

Vendas,

Mid(,14,18) as

FROM

(qvd)

join

LOAD

FROM T001W.QVD (qvd);

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 10:06

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/181575#181575

Create a table with data from 2 tables

vijay_iitkgp — Wed, 11 Jan 2012 10:45:35 GMT

Hi Can you please send me the qvs file . or Copy script in notepad and send.

Re: Create a table with data from 2 tables

Wed, 11 Jan 2012 10:53:57 GMT

If you need i can send you the qvd files, but for now i send you the script:

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='#.##0,00 €;-#.##0,00 €';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD-MM-YYYY';

SET TimestampFormat='DD-MM-YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Fev;Mar;Abr;Mai;Jun;Jul;Ago;Set;Out;Nov;Dez';

SET DayNames='seg;ter;qua;qui;sex;sáb;dom';

LOAD , // Materiais

FROM MARA.QVD (qvd);

LOAD , // Familias e Subfamilias

Mid(,1,5) as

FROM T179T.QVD (qvd);

LOAD Mid(,1,5) as , // Familias e Subfamilias

FROM T179T.QVD (qvd) Where Len() = 5;

LOAD , // Centro Abestecedor

FROM T001W.QVD (qvd);

LOAD Local, //Locais Instalação

Nome1,

FROM KNA1.QVD (qvd) where = '1300';

LOAD Local, // Local dados empresa

FROM KNB1.QVD (qvd) where = '1100';

LOAD , // Descrição segmentos empresa 3

FROM ZDM_SEGEMP3T.QVD (qvd);

VENDASBW:

LOAD , // Vendas de BW

OverPrice,

Qtd.vendas,

Vendas,

Mid(, 14,10) as Cliente,

Mid(, 14,10) as Local,

Mid(,15,7) as AnoMes,

Year(Date#(Mid(,15,4),'YYYY')) as Ano,

Month(Date#(Mid(,20,2),'MM')) as Mes,

Mid(,14,18) as

FROM

(qvd)

Join

LOAD

FROM

(qvd);

//[ZCO01-Z_CO_03_LF_GERTAL_012011.QVD](qvd);

TEMPORARIA:

LOAD Ano,

Mes,

SUM(Qtd.vendas) AS TOTALQTD,

SUM(Qtd.vendas * ) AS TOTALVENDAS

RESIDENT VENDASBW

group by Ano, Mes order by Ano, Mes ;

Cumprimentos,

Joaquim Correia

Create a table with data from 2 tables

Wed, 11 Jan 2012 14:58:35 GMT

Hi,

In the code which Vijay has sent. you need to add word table after drop keyword.

Drop table Table1;

Re: Create a table with data from 2 tables

Wed, 11 Jan 2012 18:10:06 GMT

Hello Sara.

I received the bellow message from you, but i can’t read it (and in the community forum it’s impossible to read also), and i really can’t understand if you have done any change to the code sample i send.

Thank you,

Joaquim

De: Sara Leslie

Enviada: quarta-feira, 11 de Janeiro de 2012 17:59

Para: B2B-Joaquim Correia (Informática)

Assunto: Re: - 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

reply from Sara Leslie<http://community.qlik.com/people/sli> in Development (QlikView Desktop) - View the full discussion<http://community.qlik.com/message/181585#181585

Create a table with data from 2 tables

vijay_iitkgp — Thu, 12 Jan 2012 07:18:08 GMT

Hi,

Some how I am unable to read the script as it is showing link of your subject line. Can you please attach the notepad file.