topic Re: Tables box in QlikView

Tables box

Anonymous — Sat, 25 Jan 2014 17:32:49 GMT

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?

Re: Tables box

Sat, 25 Jan 2014 18:04:31 GMT

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

Re: Tables box

Sat, 25 Jan 2014 18:11:25 GMT

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

Re: Tables box

Anonymous — Sat, 25 Jan 2014 19:31:03 GMT

Thank you for your help.

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

Re: Tables box

Anonymous — Sat, 25 Jan 2014 19:35:51 GMT

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?

Re: Tables box

Sun, 26 Jan 2014 11:22:57 GMT

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

Re: Tables box

Anonymous — Sun, 26 Jan 2014 14:09:36 GMT

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

Re: Tables box

Sun, 26 Jan 2014 14:21:51 GMT

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.

Re: Tables box

Anonymous — Sun, 26 Jan 2014 14:29:13 GMT

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

Re: Tables box

Sun, 26 Jan 2014 14:46:37 GMT

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;

Re: Tables box

Anonymous — Sun, 26 Jan 2014 14:52:18 GMT

Hi,

Thank you very much it finally worked correctly.

I eliminated the undeeded fields like you mentioned and used the script you gave me.

Thank you very much once again.

Re: Tables box

Sun, 26 Jan 2014 14:53:49 GMT

Your welcome.