Tables box

[Anonymous]

Hi,

I need help with creating a table out of data from 3 tables.

I loaded the 3 excel files with the tables needed

but when I create the table box the data I want to be shown does not show.

Basically I have 3 tabloids A,B,C

and I want to show in the table box the following SQL query:

SELECT B.field1,

A.field1,

A.field2,

A.field3,

SUM(B.field2) ,

SUM(B.field3) ,

B.field4,

C.field1,

C.field2,

FROM A, B, C

WHERE 1 = 1

AND A.field1 = B.field5

AND B.field6 = C.field3

GROUP BY B.field1,

A.field1,

A.field2,

A.field3,

B.field4,

C.field1,

C.field2,

HAVING SUM(B..field2) <> 0

is it possible?

[#2]

Yes, you can transform this query using Qlikview script.

Finaltable:

Load * ;

SQL

Select *

FROM A, B, C

WHERE 1 = 1

AND A.field1 = B.field5

AND B.field6 = C.field3

And then you use Group by using Finaltable created in qlikview or

You can load each table and then join or concatenate it to create one table.

FinalTable:

Load *;

SQL

Select * from A;

concatenate

Load *;

SQL

Select * from B;

concatenate

Load *;

SQL

Select * from C;

Then you can use it for further calculations.

The above is just a sample script. Hope it is helpful

[#3]

Hi Nadeen,

Yes it is possible but instead of "table box" use "straight table" in Qlikview

something like

field1, (Dimension)

field2, (Dimension)

field3, (Dimension)

SUM(field2) , (Expression)

SUM(field3) , (Expression)

field4  (Dimension) etc...

Hope this will help you..

-Yojas

[Anonymous]

Thank you for your help.

I tried to implement what you mentioned  but it did not work

[Anonymous]

Hi,

Thank you for your help.

I did what you mentioned and it kind of worked but no completely.

The first part, I got correctly, but the table does not get the complete correct results

because some of the fields have the same labels in two tables that is why I need to specify this to be able to choose just the data that is found in exactly the two fields:

WHERE 1 = 1

AND A.field1 = B.field5

AND B.field6 = C.field3

Group by ..

….

HAVING SUM(B..field2) <> 0

is it possible to implement it in the straight table?

[#6]

Hi Nadeen,

Yes it is possible.. If you do something like,

Store value of Field5 in some variable (v_Field5)

then in the set expression pass that value to Field 1 and using AGGR() function you can Group By it with the respective dimension (Field).

Try this and if it is not working then send me some sample... I'll do it for you

-Yojas

[Anonymous]

Hi Nadeen,

Please follow the link below Henric Cronstrom gives detail info how to merge data for multiple datasources

http://community.qlik.com/blogs/qlikviewdesignblog/2014/01/14/merging-data

[#8]

Hi Nadeen, You query looks good and this query is executed in Qlikview without errors ?

In your qvw, you have synn keys, So please drop unnecessary fields or concatenate the tables to avoid the synn keys.

[Anonymous]

Hi,

Yes it runs without errors but the data just doesn't show on the straight table when selected.

Yes I tried concatenating the tables but I had an error and with removing unnecessary fields nothing changes as well

[#10 · Accepted solution]

Hi Naden, Please find the below script:

// First Join all the tables from the files

TEMP:

LOAD BP_ID ,

  PERSON_TYPE ,

  DOMI_COUNTRY

FROM

[\\vmware-host\Shared Folders\3- Prototype\DB xls data\tmp_bsm_bp.xls]

(biff, embedded labels, table is tmp_bsm_bp$);

Inner Join (TEMP)

LOAD REF_smalldatetime ,

  QTY ,

  CURR_VAL_POS ,

  CURR_POS_REF_CURRY ,

  BP_ID ,

  ASSET_ID

FROM

[\\vmware-host\Shared Folders\3- Prototype\DB xls data\tmp_bsm_positions.xls]

(biff, embedded labels, table is tmp_bsm_positions$);

Inner Join (TEMP)

LOAD ASS_GROUP ,

  ASS_TYPE ,

  ASSET_ID

FROM

[\\vmware-host\Shared Folders\3- Prototype\DB xls data\tmp_bsm_assets.xls]

(biff, embedded labels, table is tmp_bsm_assets$);

// Calulate the sum's

FINAL:

Noconcatenate

LOAD BP_ID ,

  PERSON_TYPE ,

  DOMI_COUNTRY ,

  REF_smalldatetime ,

  Sum(QTY) AS QTY ,

  Sum(CURR_VAL_POS) AS CURR_VAL_POS ,

  CURR_POS_REF_CURRY ,

  ASS_GROUP ,

  ASS_TYPE

Resident TEMP 

Where SUM(QTY) <> 0

Group By BP_ID , PERSON_TYPE , REF_smalldatetime , CURR_POS_REF_CURRY , ASS_GROUP , ASS_TYPE;

DROP Table TEMP;