11 Replies Latest reply: Jan 26, 2014 9:52 AM by Nadeen Alkaydi RSS

    Tables box

    Nadeen Alkaydi

      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
          Angad Singh

          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
            Yojas Samarth

            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
                Nadeen Alkaydi

                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
                    Yojas Samarth

                    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
                        Srikanth P

                        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
                            Nadeen Alkaydi

                            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
                              Srikanth P

                              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
                        neetha P

                        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