Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Problem in quarter mapping (inline)

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:-

MONTH.PNG

but table viewer showing mapping correctly...

Capture.PNG

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

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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.

View solution in original post

5 Replies
Anonymous
Not applicable
Author

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.

Not applicable
Author

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

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

Not applicable
Author

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

Anonymous
Not applicable
Author

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

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

ceil(month(DATE)/6) as Half

Not applicable
Author

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