Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

1 Solution

Accepted Solutions
sridhar240784
Contributor III

Re: Getting Column Names

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

12 Replies
jagannalla
Valued Contributor III

Getting Column Names

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.

jagannalla
Valued Contributor III

Getting Column Names

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.

Not applicable

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

sridhar240784
Contributor III

Re: Getting Column Names

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

Not applicable

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

sridhar240784
Contributor III

Re: Getting Column Names

Navin,

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

Then lets check whether it is possible or not

- Sridhar

Not applicable

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

       

  

    

sridhar240784
Contributor III

Re: Getting Column Names

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

Not applicable

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

Community Browser