topic Month Field in QlikView

Month Field

NavinReddy — Thu, 29 May 2014 12:40:19 GMT

Dear All,

I have Data with out month field,

but i have data like jan,feb,mar,apr......... separated fields

how can i merge those fields into one under one month field

i tried with cross table but i am not getting could some one help me do this

i am atteching my Excel please find the attechment

Thanks in Advance

Niranjan

Re: Month Field

Thu, 29 May 2014 12:42:51 GMT

The easiest thing to do may be to create a master calendar to work with

Re: Month Field

NavinReddy — Thu, 29 May 2014 12:53:00 GMT

Hi Robert,

But i have fields like
jan Actual Dollars,

feb Actual Dollars..like this format

here if i use master calerdar it will not work there is not mapping

Sorry ,if i am wrong

Best Regards,
Niranjan

Re: Month Field

Thu, 29 May 2014 12:55:42 GMT

I hope this is made up data!

The data does not lend itself to using crosstable, as you have other fields on the rows after the month based columns.

What I do in these situations, is load it in to a temp table, then load into another table with the columns in the right positions.

so for a file with columns as follows

X,Y,jan,feb,mat.apr..... Dec, Heir1,heir2...heir10

I would

(This is pseudo code notload script!)

temp: load * from file;

data: crosstable(month,value,4) load X,Y,Heir1,Heir2,jan,feb,mar,apr,may,jun,jul,aug,sep,oct,nov,dec resident temp;

drop table temp;

Does this help?

Richard

Re: Month Field

NavinReddy — Thu, 29 May 2014 12:59:55 GMT

Hi Richard Garforth,

Thanks for your reply it was nice idea

but dont know how can apply

i atteched Excel dump could you please help me to solve

Best Regards,
Niranjan

Re: Re: Month Field

Thu, 29 May 2014 13:21:20 GMT

ok.

Attached example.

You may find http://www.mockaroo.com/ useful for generating example data.

does the attached help?

Re: Re: Month Field

its_anandrjs — Thu, 29 May 2014 13:30:30 GMT

Hi Niru,

But i suggest can you use the inline tables for this because the values are in the field labels this way is difficult.

Regards

Re: Month Field

NavinReddy — Thu, 29 May 2014 13:30:30 GMT

Thanks you Richard it will helps a lot

Best Regards,

Niranjan

Re: Month Field

Anonymous — Thu, 29 May 2014 19:24:32 GMT

Hello, Niru Niru.

Here is an example QVW using Crosstable with your data. I'm going to attach the same file to your other posts about this issue too.

Hope it helps.

Cheers.

Re: Month Field

NavinReddy — Thu, 29 May 2014 19:32:55 GMT

Hi Bruno montenegro,

it was greate answer ,my requirement will also same

Thank you so much

please if its possible give me breaf about how you did this issue

Best Regards,

Niranjan

Re: Month Field

Anonymous — Thu, 29 May 2014 20:20:14 GMT

I used Crosstable as you can see in the script. Henric Cronström has a great article about it here: The Crosstable Load

I used it twice. One for dollars and one for SQF and then I joined the two resulting tables. At the same time I transformed the name of the month fields to get proper months in QlikView.

Best regards.

Re: Month Field

NavinReddy — Thu, 29 May 2014 20:24:49 GMT

Thank you Brono Montenegro