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]

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

[vijay_iitkgp]

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;

[#4]

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

[vijay_iitkgp]

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;

[#6]

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

[vijay_iitkgp]

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

[#8]

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

as

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

[#9]

Hi,

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

Drop table Table1;

[#10]

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

[vijay_iitkgp]

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.