topic Problem in quarter mapping (inline) in QlikView

Problem in quarter mapping (inline)

Tue, 13 Mar 2012 10:14:19 GMT

hi,

i was trying to create quarter by loading inline table as:

LOAD * INLINE [

Half, MONTH_NUM, MONTH, QUARTER

1,1, Jan, Q1

1, 2, Feb, Q1

1, 3, Mar, Q1

1, 4, Apr, Q2

1, 5, May, Q2

1, 6, Jun, Q2

2, 7, Jul, Q3,

2, 8, Aug, Q3,

2, 9, Sep, Q3,

2, 10, Oct, Q4,

2, 11, Nov, Q4,

2, 12, Dec, Q4,

];

and map it to xl which contains a field date1

LOAD Date(Date1) as DATE,
Month(Date1) as MONTH
FROM
map.xlsx
(ooxml, embedded labels, table is Sheet1);

when i see table viewer , mapping is done perfectly but i am getting strange result.

the resultant MONTH field looks like:-

but table viewer showing mapping correctly...

I have attached my QVW and XL file....PLz help.......

Re: Problem in quarter mapping (inline)

Anonymous — Tue, 13 Mar 2012 10:21:06 GMT

The problem is that MONTH field in the Sheet1 table is a numeric field in date format MMM, while in the inline load it is a text field. One way to fix it is to convert it in text on the excel load, for example:
text(Month(Date1)) as MONTH

Edit:

In general, it is not a good idea to use this inline. You have Date fields in excel - use qlikview functions to create month, quarter, even half. See examples of master calendar on communuty for ideas.

Problem in quarter mapping (inline)

Tue, 13 Mar 2012 10:29:35 GMT

thanks for the reply...it works fine now..

is there any other way to fix it....:)

Problem in quarter mapping (inline)

Tue, 13 Mar 2012 10:34:20 GMT

is there any Qlikview function which can map to quarter and half directly..???

Problem in quarter mapping (inline)

Anonymous — Tue, 13 Mar 2012 12:01:12 GMT

Everything can be calcualted from the DATE field. For examle:

'Q' & ceil(month(DATE)/3) as Quarter

ceil(month(DATE)/6) as Half

Problem in quarter mapping (inline)

Wed, 14 Mar 2012 04:08:07 GMT

I got it now.....thanks alot...:)