Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
nadeenalk
New Contributor

Tables box

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?

1 Solution

Accepted Solutions
Not applicable

Re: Tables box

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;

11 Replies
Not applicable

Re: Tables box

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

Not applicable

Re: Tables box

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

nadeenalk
New Contributor

Re: Tables box

Thank you for your help.

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

nadeenalk
New Contributor

Re: Tables box

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?

Not applicable

Re: Tables box

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

neetha_p
Honored Contributor

Re: Tables box

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

Not applicable

Re: Tables box

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.

nadeenalk
New Contributor

Re: Tables box

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

Not applicable

Re: Tables box

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;

Community Browser