Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
mov
Not applicable

Re: Problem in quarter mapping (inline)

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.

5 Replies
mov
Not applicable

Re: Problem in quarter mapping (inline)

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

Problem in quarter mapping (inline)

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

is there any other way to fix it....Smiley Happy

Not applicable

Problem in quarter mapping (inline)

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

mov
Not applicable

Problem in quarter mapping (inline)

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

Problem in quarter mapping (inline)

I got it now.....thanks alot...Smiley Happy