12 Replies Latest reply: Aug 24, 2011 9:09 AM by Sridhar Ethiraj RSS

    Getting Column Names

      Hi,

           I have a table where the columns keep changing based on the current month.Which is if I have Sep as me current month then

           columns will start from Sep 2011(if sep is current month) to Aug 2012.

       

         

           I have to populate a straight table based on these columns and now these columns cannot be static as the column names keep changing

           for every month.

       

           Could you please suggest a way where I can populate the Grid so that no matter the column names change I need the staright5 table getting populated

           with the field names present in the table (though the field names aree changing).

       

      Regards

      Navin.G

        • Getting Column Names
          Jagan Nalla

          Hello,

          1.How are you changing the column names?

          2. What are the fields in edit script?

          3. If the fields are constant for every reload?

           

          Give me any example file how you are doing?

           

          We can show changed the columns in straight table dynamically. But we need to catch the changed column names first.

            • Getting Column Names
              Jagan Nalla

              Navin,

              You can add to Striaght table dynamically. But before you want to add changed columns to striaght table you need to stored all changed columns in one variable.

              For eg:

              vColumns=Changed_A,Changed_B,Changed_C,Changed_D

              Changed_A,Changed_B,Changed_C,Changed_D this should be fields loaded in your editscript.

              Now take one button and add action with below macro code.

              sub StriaghtTable_Add

                     

                      set vColumns=ActiveDocument.Variables("vColumns")

                      set STB = ActiveDocument.GetSheetObject("CH01")

                    

                      for i=1 to STB.GetColumnCount

                          TB.RemoveField 1

                      next

               

                     ArrFields = split(vColumns.GetContent.String, ",")

                    

                     for i=0 to Ubound(ArrFields)

                              STB.AddDimension ArrFields(i)

                      next

              end sub

               

              Hopes this will helps you.

                • Re: Getting Column Names

                  Hi Jagan,

                               The columns get changed.I have created an Sql procedure for this and that

                               procedure changes the columns.

                   

                               The first column would be the 'currentmonth+ CurrentYear'

                   

                               The second column would be 'next month (current month+1)'+Current Year'

                   

                               and so on.

                   

                               Ex:If August is the current month then first column will be 'Aug 2011' and next 'Sep 2011'.These columns go on till 'Jul 2012'

                                     Inshort the columns vary from the current month of this year to the previous month of next year.

                   

                              Please suggest a way forward.

                   

                  Regards

                  Navin.G

              • Re: Getting Column Names
                Sridhar Ethiraj

                Hi Navin,

                 

                If i understand you properly, you could solve this using CrossTable functionality in Qlikview.

                 

                Look at the below example.

                 

                Your Raw Data from DB:

                 

                Region, Jun 2011, Jul 2011, Aug 2011                    //Field Names.

                A,          1,            2,              3

                B,          4,            5,              6

                C,          7,            8,              9

                 

                You can do the cross table load to load this data.

                 

                Crosstable(Month,Data,1)

                Load * from Raw_data_DB;

                 

                This will load the months (i.e. Jun 2011, Jul 2011, Aug 2011) in Month field.

                 

                Now you can create a straight table with Region as 1st dimension, Month as 2nd Dimension and Data as expression and you can drag the Month to horizontal position in pivota table.

                 

                Hope my understanding on you problem is clear, if not give us more details on your problem.

                 

                - Sridhar

                  • Re: Getting Column Names

                    Hi Sridhar,

                                    I think this might solve me problem but where I am getting stuck up is I have more than two dimensions

                     

                                   Is a cross table possible with more than two dimensions?

                     

                                   I think it is not possible.Pleas let me know.

                     

                    Regards

                    Navin.G

                      • Re: Getting Column Names
                        Sridhar Ethiraj

                        Navin,

                         

                        Can you please give an example and explain us your requirement?

                         

                        Then lets check whether it is possible or not

                         

                        - Sridhar

                          • Re: Getting Column Names

                            Hi Sridhar,

                                           Excluding the month columns I have 11 more columns.

                             

                                           Ex:Rig,Craft,Plan,Instruction.....11 columns in this way.The rest are Sep 2011,Oct 2011,...Feb 2013.

                             

                                           Now these months I cannot manually give them as dimensions as in the next month the columns

                                           will start from Oct 2011,Nov 2011....Mar 2013.

                             

                                           These dimensions come from a table which will have these months changed based on the months.

                             

                                          These columns are having data something like

                                          Sep 2011 Oct 2011 Nov 2011...............Feb 2013

                                          90|4.0       120|3.0   15|0.0                    90|3.0

                             

                                         So finally my Straight table will look like

                                         Rig   Craft     Plan     Instructions          Sep 2011    Oct 2011   .........Feb 2013

                                           A      B        C         ABFGT                  90|4.0        120|3.0              90|3.0

                             

                                         If I reload the Report on Sep then:

                                          Rig   Craft     Plan     Instructions          Oct 2011    Nov 2011   .........Mar 2013

                                           A      B        C         ABFGT                  90|4.0        120|3.0              90|3.0

                             

                                        Please suggest a way forward.

                             

                            Regards

                            Navin.G

                                   

                              

                                

                              • Re: Getting Column Names
                                Sridhar Ethiraj

                                 

                                Hi Navin,

                                 

                                What i have understood from you is, You have data in your table as illustarted below and you want that to be load in QlikView.

                                 

                                Transaction Table (Source Table):

                                 

                                Rig   Craft     Plan     Instructions          Sep 2011    Oct 2011   .........Feb 2013

                                A      B        C         ABFGT                  90|4.0        120|3.0              90|3.0

                                 

                                If that is your requirement. You can do Cross table like below and load in Qlikview.

                                 

                                CrossTable(Month,Data,4)

                                Load * From Transaction_table;

                                 

                                Explanation for Above Script:

                                 

                                Here "Load *" says to Qlikview to load all data from Transaction Table regardless of any filed names (If field names changes also "*" wildcard accepts it).

                                 

                                In Cross Tabel :

                                 

                                          Last Parameter  '4' - Last parameter 4 in Cross table Query tells Qlikview that 1st 4 field are qualifying field and this has to be skipped from the cross table.

                                 

                                          Month Field - Load all field names under that Field Month.

                                    

                                          Data Field - Load all values under Data field.

                                 

                                Hope this clears your doubt.

                                 

                                Search throug this forum, Cross table has been discussed many time in this forum.

                                 

                                -Sridhar

                                  • Re: Getting Column Names

                                    Hi Sridar,

                                                  Thanks for all your help till now.I feel I am getting close to a solution.

                                           

                                                  Please let me know whether I need to put these months as Dimensions in a Straight Table

                                                  or as Expressions.

                                     

                                                  I have Loaded the Script as you have said now please let me know what has to be done with

                                                  regards to the straight table.

                                     

                                    Regards

                                    Navin.G

                                      • Re: Getting Column Names
                                        Sridhar Ethiraj

                                        Navin,

                                         

                                        Insted of using Straight table, use pivot table and month should be as Dimension. Drag this month to the end of the pivot table toward right horizontally.

                                         

                                        -Sridhar

                                          • Re: Getting Column Names

                                            Hi Sridhar,

                                                           Thanks man Thanks a lot this was what I needed.My only issue now is these columns

                                                           i.e.Sep 2011 Oct 2011...Feb 2013 need to be aranged in an order.Instead I am getting the foll

                                             

                                                           May 2012  Jun2011 Dec 2012........Actually they do not follow any order as such.

                                             

                                                           Could you suggest a way to arrange them in an order.

                                             

                                                           Some thing like :Sep 2011 Oct 2011 Nov 2011....Feb 2013

                                             

                                            Regards

                                            Navin.G