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]

Hello Vijay.

I know, i have the same problem with some of the mails i receive from qliktech (even in the community site some of my mails are in a strange format).

Nevertheless i send you an attachment with the script.

But i think that maybe i’ve not explained myself correctly.

Many of your colleagues at cliktech have answered me but none have answered my real problem.

And my real problem is:

I have 2 tables, each one with diferente columns, related by one single column (Local) and i need to create a temp table with data from both tables:

LOCAISINSTALACAO:

LOAD Local

…

,

…

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

VENDASBW:

LOAD …

Local,

,

Qtd.vendas,

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

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

…

FROM

(qvd);

I need to create a temp table with data from both tables.

The SQL code would be something like this:

Select a.Ano, a.Mês, b.[Centro fornecedor], SUM(a.Qtd.vendas) AS TOTALQTD, SUM(a.Qtd.vendas * a.[Preço Líquido Unitário]) AS TOTALVENDAS

From VENDASBW a, LOCAISINSTALACAO b

Where a.local = b.local

Group by a.ano, a.mes, b.[Centro fornecedor]

Order by a.ano, a.mes, b.[Centro fornecedor]

In qlikview should be something like this

TEMPTABLE:

LOAD Ano,

Mes,

,

SUM(Qtd.vendas) AS TOTALQTD,

SUM(Qtd.vendas * ) AS TOTALVENDAS

RESIDENT VENDASBW, RESIDENT LOCAISINSTALACAO

group by Ano, Mês,

order by Ano, Mês, ;

Thank you,

Joaquim

De: Vijay Singh

Enviada: quinta-feira, 12 de Janeiro de 2012 07: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 Vijay Singh<http://community.qlik.com/people/vijay_iitkgp> in Development (QlikView Desktop) - View the full discussion<http://community.qlik.com/message/181912#181912

[vijay_iitkgp]

Hi,

I think I figured out the error:

In below table culprit is

SUM(Qtd.vendas * [Preço Líquido Unitário]) AS TOTALVENDAS.

Here you are using [Preço Líquido Unitário] but it is not in Group by condition. It is better you do this in

VENDASBW table as Qtd.vendas * [Preço Líquido Unitário] AS TOTALVENDAS and in TEMPORARIA table use

Sum(TOTALVENDAS) as TOTALVENDAS

TEMPORARIA:

LOAD Ano,

     Mes,

SUM(Qtd.vendas) AS TOTALQTD,

SUM(Qtd.vendas * [Preço Líquido Unitário]) AS TOTALVENDAS

RESIDENT VENDASBW

group by Ano, Mes order by Ano, Mes ;

[#4]

Hello again and thank you Vijay.

I think you didn’t get it.

That is not my problem, i know that i can do what you said, but my real problem is to have a temp table with columns from 2 existing tables.

How to join them?

Please read my previous message.

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: quinta-feira, 12 de Janeiro de 2012 09:30

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

[vijay_iitkgp]

Ohhhh... But this was also the error.

Can you please let me know which two table you want to join and on which key ??

[#6]

The two tables are:

LOCAISINSTALACAO

VENDASBW

Key: Local

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: quinta-feira, 12 de Janeiro de 2012 10:01

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

[vijay_iitkgp]

You Can Si,mply do it by

Temp:

Load

Local,

Field A,

FieldB

Resident

LOCAISINSTALACAO;

Join or Inner Jon whatever you need

Load

Local,

Field D,

Field E

Resident VENDASBW;

[#8]

Thank you Vijay.

I think this is it.

I can´t try it now (out of office) but when i do i’ll let you know.

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: quinta-feira, 12 de Janeiro de 2012 10:34

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

[#9]

Hello again Vijay.

After all it was almost it ☺

You forgot the sums.

What i need in the end is the following:

Imagine the 2 tables i send you

LOCAISINSTALACAO:

LOAD Local,

Nome1,

,

...

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

VENDASBW:

LOAD ...

,

Qtd.vendas,

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,

...

FROM

(qvd);

My temp table has the following structure:

TEMPORARIA:

LOAD Ano,

Mes,

SUM(Qtd.vendas) AS TOTALQTD,

SUM(Qtd.vendas * ) AS TOTALVENDAS

RESIDENT VENDASBW

group by Ano, Mes order by Ano, Mes ;

If i had the following data in LOCAISINSTALACAO:

Local

Nome1

Centro fornecedor

1

John

1

2

George

1

3

Mary

2

4

Francis

4

And the following data in VENDASBW:

Preço Líquido Unitário

Qtd.vendas

Local

Ano

Mes

2

1

1

2012

1

2

1

1

2012

1

2

1

2

2012

2

2

1

2

2012

2

2

1

3

2012

1

2

1

3

2012

2

2

1

4

2012

1

2

1

4

2012

2

What i get in my actual temp table will be the following data:

TEMPORARIA (actual data)

Ano

Mes

TOTALQTD

TOTALVENDAS

2012

1

4

8

2012

2

4

8

But, and this is the importante part, what i need to get is:

TEMPRARIA (needed data)

Ano

Mes

Centro fornecedor

TOTALQTD

TOTALVENDAS

2012

1

1

2

4

2012

1

2

2

4

2012

2

1

2

4

2012

2

2

2

4

Joaquim

[Sarkis22]

Does anyone know how to interact with cubes (I don´t know its nature) and Qlik Sense?. I need to make them interact, what if I had only read access to the cube and need to add more data from an excel sheet. Now that is not possible because of permissions but is is possible to build up a temp table between the cube and the excel sheet in order to execute queries in it???

Thank you in advance