Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
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.
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
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
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
Navin,
Can you please give an example and explain us your requirement?
Then lets check whether it is possible or not
- Sridhar
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
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
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