How do I assign a month value to a field?

[Anonymous]

I have an excel document that has many columns that include in the name the month. My question is, is there a way to assign these fields to the specific month?

In example I have lists named July 2012 GP,July 2013 GP, August 2012 GP,August 2013 GP, September 2012 GP,September 2013 GP, October 2012 GP, October 2013 GP, etc... I want to have a list that lists all twelve months, and when I select a month I want a bar graph that I am working with to display the info from the lists with the corresponding names

Is there a way to do this?

Thank you

[#2]

Joe,

You can use "space" as a delimiter for parsing, if you have space in the fieldname value.

For Eg. (as used in the attached file previously): 

SubField (Date_time,' ',2)

here I have used "space" as a delimiter.

Hope this gives you a solution to your problem!

[Anonymous]

I do believe I am getting it! I am missing one key part to the full solution.

In the load its:

Directory;
Load_Doc:
Load
[Name],
[July 2012..],
[July 2013..],
...
From
[excel.xls]
(biff, embedded labels, table is [MASTER$])
Where [Name] <> 'REPORT TOTALS' AND [Name] <> 'validation';

Then I have
Temp:
Load *,
left(Date_time,3) as Month,
SubField(Date_time,' ',2) as Year
Resident Load_Doc;

but when I reload the data, it tells me the the field Date_time cannot be found, but when I include Date_time in my initial load it tells me the temp table cannot be found. Am I transposing this correctly or have I mistaken a step or two?

Again I thank you for all your help this far!

[#4]

To be able to use Date_time in Temp table (which is resident Load_Doc), Date_time should be available In Load_Doc and ultimately should either be available in your source data or should be loaded using "Inline".

The script in your snapshot does not have Date_time as a field in Load_Doc.

PFA a sample excel file and qvw based on that.

[Anonymous]

Ok I see what you are saying, would this work with column names? Because that is how the spreadsheet I am working with is.

I did try putting in the name of a column in the  temp table that is created, but when I reload the data Qlikview becomes unresponsive. So I am guessing that it is more than just using the column name.

Rather than trying to explain what I am using, I have attached a document that mimics the spreadsheet that I am using.

Thank you for your help with my question!

[#6 · Accepted solution]

No, it would not work if January 2012 GP, February 2012 GP etc. are column names, the way I have suggested will work only if these are column values (which was my understanding earlier).

Right now I can't think of a way to extract year and month from these column names.

Yes ofcourse it can be done in the chart individually for each expression but it will not solve your purpose.

[Anonymous]

Well I do thank you for your patience and all your help. I have marked your answer as correct as you have led me to the conclusion about my data.

Again thank you for all of your advice, it has helped me understand more functions in Qlikview.