topic Re: Getting Column Names in QlikView

Getting Column Names

Tue, 23 Aug 2011 07:44:28 GMT

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

jagannalla — Tue, 23 Aug 2011 07:52:19 GMT

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

jagannalla — Tue, 23 Aug 2011 08:35:23 GMT

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

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

for i=0 to Ubound(ArrFields)

STB.AddDimension ArrFields(i)

end sub

Hopes this will helps you.

Re: Getting Column Names

Tue, 23 Aug 2011 09:11:32 GMT

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

sridhar240784 — Tue, 23 Aug 2011 09:28:55 GMT

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

Tue, 23 Aug 2011 13:23:21 GMT

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

sridhar240784 — Tue, 23 Aug 2011 13:31:49 GMT

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

Wed, 24 Aug 2011 04:01:37 GMT

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

sridhar240784 — Wed, 24 Aug 2011 04:32:50 GMT

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

Wed, 24 Aug 2011 06:31:39 GMT

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

sridhar240784 — Wed, 24 Aug 2011 06:35:57 GMT

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

Wed, 24 Aug 2011 09:40:50 GMT

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

Re: Getting Column Names

sridhar240784 — Wed, 24 Aug 2011 13:09:43 GMT

Navin,

Are you loading these Month as a text? if So try to convert it as date and extract month from it. it wil automatically work.

-Sridhar