11 Replies Latest reply: Nov 27, 2013 1:13 AM by Neha Saxena RSS

    How to join two crosstab tables?

    Neha Saxena

      Hi Experts,

       

      I have created 3 crosstab tables in my app. Now i want to join these tables but when  i am trying this with concatenate function but its not working...

      Pls help me

      Thanks in Advance!!

        • Re: How to join two crosstab tables?

          Hi,

               Can you please attach your sample data. It will be easy to understand the problem.

           

          Thanks,

          Ashutosh

            • Re: Re: How to join two crosstab tables?
              Neha Saxena

              Thanks for the reply!

               

              Here is my script:

              Fault:
              CrossTable(F1, Data1,3)
              LOAD CLUSTERNO as F_Cluster1,
                INSTANCE as F_INSTANCE1,
              "EE_FLIGHTCNT" AS F_FlightCnt,
                  ALT ,
                  MN ,
                  IAS;
              SQL SELECT *
              FROM "DPHM_V1".FAULT;

              Concatenate(Fault)
              CrossTable(F2, Data2,3)
              LOAD CLUSTERNO as F_Cluster2,
                INSTANCE as F_INSTANCE2,
                  N1IN,
                  N2IN ,
                  P3IN;
                  SQL SELECT *
              FROM "DPHM_V1".FAULT;

               

              I am getting error - Illegal combination of prefixes.

            • Re: How to join two crosstab tables?

              Please attach your sample script

              • Re: How to join two crosstab tables?
                Jerry Tan

                Hi,

                 

                You can try to transpose the crosstab table first and then only use the concatenate function.

                • Re: How to join two crosstab tables?
                  Manish Kachhia

                  Difficult to understand without knowing your data or script.

                  need to either use concatenate or join function if necessary but accurate answer can be given only after knowing your data.

                  • Re: How to join two crosstab tables?
                    Kumar Raman

                    Hi Neha,

                     

                    chk the below script..hope t helps..

                     

                    Fault:

                    CrossTable(F1, Data1,3)
                    LOAD CLUSTERNO as F_Cluster1,
                    INSTANCE as F_INSTANCE1,
                    "EE_FLIGHTCNT" AS F_FlightCnt,
                    ALT ,
                    MN ,
                    IAS;
                    SQL SELECT *
                    FROM "DPHM_V1".FAULT;

                    Temp_Fault:
                    CrossTable(F2, Data2,3)
                    LOAD CLUSTERNO as F_Cluster2,
                    INSTANCE as F_INSTANCE2,
                    N1IN,
                    N2IN ,
                    P3IN;
                    SQL SELECT *
                    FROM "DPHM_V1".FAULT;

                    NoConcatenate
                    Fault1:
                    Concatenate(Fault)
                    load *
                    Resident Temp_Fault;
                    drop table Temp_Fault;


                    Regards

                    kumar

                      • Re: How to join two crosstab tables?
                        Neha Saxena

                        Hi Kumar Raman,

                         

                        Thanks for your help. Its working fine.

                        But what would be the changes in script if i have more than 2 tables for concatenate.

                        Fault:
                        CrossTable(F1, Data1,3)
                        LOAD CLUSTERNO as F_Cluster1,
                          INSTANCE as F_INSTANCE1,
                        "EE_FLIGHTCNT" AS F_FlightCnt,
                        ALT ,
                            MN ,
                            IAS;
                        SQL SELECT *
                        FROM "DPHM_V1".FAULT;

                        Concatenate(Fault)
                        Fault2:
                        CrossTable(F2, Data2,3)
                        LOAD CLUSTERNO as F_Cluster2,
                          INSTANCE as F_INSTANCE2,

                        N1IN,
                            N2IN ,
                            P3IN;
                            SQL SELECT *
                        FROM "DPHM_V1".FAULT;

                        Fault3:
                        CrossTable(F3, Data3,3)
                        LOAD CLUSTERNO as F_Cluster3,
                        INSTANCE as F_INSTANCE3,
                        ENGVIB ,
                        MOTIN ,
                            MOPIN ;
                             SQL SELECT *
                        FROM "DPHM_V1".FAULT;

                         

                         

                        My actual script is having more than 2 tables. 

                          • Re: How to join two crosstab tables?
                            Bill Markham

                            Neha

                             

                            How about CrossTable 'ing all your tables individually first with no concatenates,

                             

                            Then concatenating the resultant tables from resident into your target table name.

                             

                            Then dropping the temporary CrossTable 'd tables.

                             

                             

                            Best Regards,     Bill

                            • Re: How to join two crosstab tables?
                              Kumar Raman

                              Hi Neha,

                               

                              Has Bill said its better to take resident and concatenate to your target table and drop them.

                              may be like the below script..

                               

                              Fault:

                              CrossTable(F1, Data1,3)
                              LOAD CLUSTERNO as F_Cluster1,
                              INSTANCE as F_INSTANCE1,
                              "EE_FLIGHTCNT" AS F_FlightCnt,
                              ALT ,
                              MN ,
                              IAS;
                              SQL SELECT *
                              FROM "DPHM_V1".FAULT;

                              Temp_Fault:
                              CrossTable(F2, Data2,3)
                              LOAD CLUSTERNO as F_Cluster2,
                              INSTANCE as F_INSTANCE2,
                              N1IN,
                              N2IN ,
                              P3IN;
                              SQL SELECT *
                              FROM "DPHM_V1".FAULT;

                              NoConcatenate
                              Fault1:
                              Concatenate(Fault)
                              load *
                              Resident Temp_Fault;

                              drop table Temp_Fault;

                              Temp_Fault3:
                              CrossTable(F3, Data3,3)
                              LOAD CLUSTERNO as F_Cluster3,
                              INSTANCE as F_INSTANCE3,
                              ENGVIB ,
                              MOTIN ,
                              MOPIN ;
                              SQL SELECT *
                              FROM "DPHM_V1".FAULT;

                              NoConcatenate
                              Fault3:
                              Concatenate(Fault)
                              load *
                              Resident Temp_Fault3;

                              drop table Temp_Fault3;

                          • Re: How to join two crosstab tables?
                            Srikanth P

                            First of all, we can't join or concatenate the two or more cross tables in qlikview. Its illegal syntax.

                             

                            So first you need to create the TEMP tables with Cross Table after that concatenate all the temp tables by using resident tables. If the temp tables have more no of rows then create TEMP qvd's and then concatenate all the TEMP qvd's.

                            • Re: How to join two crosstab tables?
                              Neha Saxena

                              Hi kumar raman,

                              Thanks a lot!! Its working fine.