topic Re: Re: How do I assign a month value to a field? in QlikView

How do I assign a month value to a field?

Anonymous — Wed, 11 Dec 2013 21:28:16 GMT

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

Re: How do I assign a month value to a field?

Wed, 11 Dec 2013 21:45:09 GMT

Hello,

Look this attached example.

Best Regards.

Re: How do I assign a month value to a field?

Thu, 12 Dec 2013 08:36:00 GMT

Hi Joe,

Might be something like attached.

Hope this helps!

Re: How do I assign a month value to a field?

Anonymous — Thu, 12 Dec 2013 14:33:10 GMT

It is very similar, but can this be reworked to include fields that I can put on the sheet? Thats what I am trying to really figure out, if I can apply a month to a field that has been added to the sheet.

I have recreated what you gave as an example but I am trying to figure out how to tie it to my existing data, is there any way to include this in my LOAD statement that includes the from?

Thanks for your help this far.

Re: How do I assign a month value to a field?

Fri, 13 Dec 2013 00:12:25 GMT

TRy to concatenate an INLINE Load statement

Re: Re: How do I assign a month value to a field?

Fri, 13 Dec 2013 06:32:11 GMT

Not sure if I understand exactly what you mean.

But if you want to have this month calculation in your script (in your LOAD statement), then you might consider doing something like:

Temp:

Load *,

left(Date_time,3) as MMM

From .....................;

I am assuming that you have a field Date_time in the given format (January 2013 GP etc.) from your database/source file.

Once you do this in the script in your LOAD statement then you can use "Month" directly in a list box in front end.

Let me know if this is what you are looking for.

Re: Re: How do I assign a month value to a field?

Fri, 13 Dec 2013 09:19:39 GMT

Try This:

Set in Main Tab Script

....

SET MonthNames='Jan;Feb;Mar;Apr;....'

Temp:

Load *,

left((subfield('Date_time',' ',1)),3) as MMM

From .....................;

Re: How do I assign a month value to a field?

Anonymous — Fri, 13 Dec 2013 14:39:35 GMT

Thats what makes this difficult, there is no date of any kind in the source, the column names is where I get the months from. I want to get as close as I can get to dynamically setting a month and a year to a column as I can get. Right now I can set up the bar graph how I want it, but I can't sort it out by month, or year.

I made an inline that looks like this:

LOAD * INLINE [

FieldName, Month, Year

July 2012 Total GP, July, 2012

July 2013 GP, July, 2013

...

];

Now other than concatenating, is there any way to link that field column to the column with the same name? In the column FieldName are names of existing fields (columns) from the spreadsheet I am using.

With this it created 3 list boxes, FieldName, Month, and Year. When I click on August in the Month List box, I want the data from all the other list boxes to be grayed out if I do not assign it August, then if I click the 2013, I only want to have the August 2012 GP list box to gray out leaving August 2013 GP to be the only "usable" data. I hope I was able to better explain what I am looking for.

Again thank you for your help.

Re: Re: How do I assign a month value to a field?

Mon, 16 Dec 2013 05:51:17 GMT

If you don't have a date clumn in your source data then you can pull month and Year from "FieldName" itself in the script.

PFA. Hope this is what you are looking for.

Re: Re: How do I assign a month value to a field?

Anonymous — Mon, 16 Dec 2013 16:40:47 GMT

That would actually be beneficial, and to do so I would assume I would have to set a delimiter of some sort to parse the names of the fields? I believe that would be the most dynamic way of assigning a month and year. So I do believe that would be what I am looking for.

Thank you

Re: Re: Re: How do I assign a month value to a field?

Tue, 17 Dec 2013 05:08:12 GMT

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!

Re: How do I assign a month value to a field?

Anonymous — Tue, 17 Dec 2013 15:05:06 GMT

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!

Re: Re: How do I assign a month value to a field?

Wed, 18 Dec 2013 04:59:48 GMT

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.

Re: Re: Re: How do I assign a month value to a field?

Anonymous — Wed, 18 Dec 2013 14:53:57 GMT

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!

Re: How do I assign a month value to a field?

Thu, 19 Dec 2013 06:46:32 GMT

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.

Re: How do I assign a month value to a field?

Anonymous — Thu, 19 Dec 2013 14:43:14 GMT

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.