4 Replies Latest reply: Aug 29, 2014 7:42 PM by Marco Wedel RSS

    Insert a new column in script

      Hi all,

       

      I have input a table with columns UWYear, DevQuarters and DevP. I've also had a main data table with UWYear and DevQuarters after CrossTable. My question is how I can insert the DevP into the main data table, please? (DevP is constrained by both UWYear and DevQuarters.)

       

      Many thanks,

       

      Grace

        • Re: Insert a new column in script
          Marco Wedel

          please post sample data of both tables

            • Re: Re: Insert a new column in script

              Hi Marco,

               

              Please find attached the sample. For both Development and Data tabs, I've done CrossTable function to them in Qlikview script so that we have UWYear as one field, and then DevelopmentQuarters (2006Q1, 2006Q2, 2006Q3, etc) as another field so that I have the contents in Development tab as field A(DevelopmentP) and the contents in Data tab as field B(Data). I'm aiming for creating a final table having four fields, UWYear, DevelopmentQuarters, DevelopmentP and Data for use.

               

              Thanks,

               

              Grace

                • Re: Re: Re: Insert a new column in script
                  Marco Wedel

                  Hi,

                   

                  one solution could be:

                   

                  QlikCommunity_Thread_131237_Pic1.JPG.jpg

                   

                  QlikCommunity_Thread_131237_Pic2.JPG.jpg

                   

                  tabFinal:
                  CrossTable (DevelopmentQuartersTxt, DevelopmentP)
                  LOAD *
                  FROM [http://community.qlik.com/servlet/JiveServlet/download/597887-122450/DevelopmentSample.xlsx] (ooxml, embedded labels, table is Development);
                  
                  tabTemp:
                  CrossTable (DevelopmentQuartersTxt, Data)
                  LOAD *
                  FROM [http://community.qlik.com/servlet/JiveServlet/download/597887-122450/DevelopmentSample.xlsx] (ooxml, embedded labels, table is Data);
                  
                  Join (tabFinal) LOAD * Resident tabTemp;
                  DROP Table tabTemp;
                  
                  Left Join (tabFinal)
                  LOAD Distinct
                    DevelopmentQuartersTxt,
                    Dual(DevelopmentQuartersTxt, MakeDate(Left(DevelopmentQuartersTxt,4),Right(DevelopmentQuartersTxt,1)*3-2,1)) as DevelopmentQuarters
                  Resident tabFinal;
                  

                   

                  hope this helps

                   

                  regards

                   

                  Marco

              • Re: Insert a new column in script
                Tresesco B

                May be like:

                 

                Final:

                Load

                          UWYear,

                          DevQuarters,

                          DevP

                From <>;

                Right Join

                Load

                          UWYear,

                          DevQuarters

                From <Maintable>;