8 Replies Latest reply: Jun 18, 2013 5:21 AM by Valentin Irimia RSS

    Crosstable on 2 joined qvd's

      Hello,

      I have a question, if you can help me, I would be grateful. I am beginner in Qlikview development.

       

      I have 2 qvd's with below structure:

       

           qvd1 :            product code

                                product name                       

                                product type

                                <specific columns of qvd1>

                                KPI1

                                KPI2

                                KPI3

       

           qvd2:             product code

                                product name                         

                                product type

                                 <specific columns of qvd2>

                                KPI4

                                KPI5

                                KPI6

                                KPI7

                                 

      What I want to do: In the loading script I want to do a inner join one these 2 qvd: qvd1&qvd2 based on product code to obtain a structure like:

       

                                product code

                                product type

                                product name

                               <specific columns of qvd1>

                               <specific columns of qvd2>

                                KPI1

                                KPI2

                                KPI3

                                KPI4

                                KPI5

                                KPI6

                                KPI7

      Then, based on this joined table, I want to make a crosstable: crosstable (kpi,data,5).

       

      In the loading script I have:

       

      table1:

           LOAD * from qvd1;

      inner join (table 2)

             LOAD * from qvd2;

       

      I guess that in join I will obtain something like:

                                product code

                                 product name                      

                                product type

                                <specific columns of qvd1>

                                KPI1

                                KPI2

                                KPI3

                               <specific columns of qvd2>

                                KPI4

                                KPI5

                                KPI6

      How can I 'put' a crosstable like on : product code, product name, product type,<specific columns of qvd1>,<specific columns of qvd2>  and KPI.

      as I don't have the columns arranged? Something like: crosstable (KPI,Data,5)           

       

      Thank you very much!!

        • Re: Crosstable on 2 joined qvd's
          Vishwaranjan Kumar

          try according to this format

           

           

          qvd1:

          load

          product code,

            product name,                       

            KPI1 ,

          product type,

            KPI3,

          KPI2,

          KPI3

          from qvd1.qvd

           

          inner join

           

          qvd2:

          load

          product code,

            product type,

             KPI4,

              KPI5,

             KPI6,

               product name,

                  KPI7

          from qvd2.qvd;

           

          Data:

          crosstable (kpi,data,3)

          load *

          resident qvd1;

            • Re: Crosstable on 2 joined qvd's

              Hi,

              U can try this also

               

              RawData1:

              load

              product code,

                product name,                       

                KPI1 ,

              product type,

                KPI3,

              KPI2,

              KPI3

              from qvd1.qvd

               

               

               

              Inner Join

               

              RawData2:

              load

              product code,

                product type,

                 KPI4,

                  KPI5,

                 KPI6,

                   product name,

                      KPI7

              from qvd2.qvd;

               

              BaseData: 

               

              CrossTable(KPI,Data,3)

                                        product code 

                                        product type

                                        product name

                                        KPI1

                                        KPI2

                                        KPI3

                                        KPI4

                                        KPI5

                                        KPI6

                                        KPI7

              Resident RawData1 ;

               

              Hope it will work.

               

               

              regards-Bika

               

            • Re: Crosstable on 2 joined qvd's
              Vishwaranjan Kumar

              i,m providing the sample.

               

              qvd1:

               

              LOAD * INLINE [

                  A, B

                  1, AA

                  2, CC

                  3, BB

              ];

               

              Inner Join

               

              qvd2:

              LOAD * INLINE [

                  A, C

                  1, XX

                  4, YY

              ];

              Data:

              crosstable (kpi,data,1)

              LOAD * Resident qvd1;

               

              see attachement

                • Re: Crosstable on 2 joined qvd's

                  Hello,

                  Thank you for your fast response!

                  I did some tests on something I've got:

                  qvd1:

                   

                   

                  LOAD * INLINE [

                      A, B, B1

                      1, AA, AAA,

                      2,  CC, CCC

                      3,  BB, DDD

                  ];

                   

                   

                  Inner Join

                   

                  qvd2:

                  LOAD * INLINE [

                      A, A2, C , C1

                      1, 100, XX, XXX

                      4, 400, YY, YYY

                  ];

                  Data:

                  crosstable (kpi,data,1)

                  LOAD * Resident qvd1;

                   

                  I have A2 represented as a 'KPI'-data of atributes, even I want it as an atribute column like A.

                   

                  What should I put in crosstable (kpi,data,1) if I have a different number of  attribute columns in first table than in the second one (in my case 1 in first:A, 2 in second: A and A2)?

                    • Re: Crosstable on 2 joined qvd's
                      Vishwaranjan Kumar

                      Read the cocepts of crosstable.

                      Crosstable

                      A cross table is a common type of table featuring a matrix of values between two orthogonal lists of header data. To turn a cross table into a straight table, use a crosstable prefix.

                      The syntax is:

                      crosstable (attribute field , data field ,Qualifier)

                      Crosstable Wizard

                      The crosstable wizard is dialog driven method of creating the crosstable syntax. This dialog is opened by clicking the Crosstable button in the Options page of the File Wizard. The crosstable wizard holds the following options:

                        where: 

                      Qualifier FieldsThe number of qualifier fields that precede the fields to be transformed.
                      Attribute FieldThe name of the new field that will contain all the fields (attribute values) to be transformed.
                      Data FieldThe name of the new field that will contain the data of the attribute values.
                      • Re: Crosstable on 2 joined qvd's

                        I mean my structure is QVD1: 13 columns and 6 Kpi

                        vs

                        QVD2: 7 columns and 9 Kpi

                        The joining columns are 2.

                        CrossTable on join will be Crosstable(Kpi,Data, 7)? If yes, 13-7 =6 columns will be treated as Kpi's.

                        How do I 'refer' them?

                         

                        Thx!

                        • Re: Crosstable on 2 joined qvd's
                          Vishwaranjan Kumar

                          hi valentinirimia

                          if your problem resolved then marked it as either correct or helpful according to your question so that it might be helpful for other developer

                          Regards

                          vishwaranjan