topic How do i split these data in a field into 2 different fields in QlikView

How do i split these data in a field into 2 different fields

Sun, 02 Nov 2014 09:53:04 GMT

Hello,

Find attached, I have in a field called "Month" Jan to Dec i want to split the first 6 months in a field and the next 6 month in another field. I want to put both in separate listbox.

Rgds

Ema

Re: How do i split these data in a field into 2 different fields

ashfaq_haseeb — Sun, 02 Nov 2014 10:05:53 GMT

Hi,

Try like this

LOAD 
     key,
     Date#(Month,'MMM') as Month,
      if(num(month((Date#(Month,'MMM'))))<=6,Month) as Month1,
      if(num(month((Date#(Month,'MMM'))))>6,Month) as Month2
FROM
split.xlsx
(ooxml, embedded labels, table is Sheet1);

Regards

ASHFAQ

Re: How do i split these data in a field into 2 different fields

Sun, 02 Nov 2014 10:11:20 GMT

Thanks Ashfaq

What if the field is not in date format. what do we do?

Re: How do i split these data in a field into 2 different fields

ashfaq_haseeb — Sun, 02 Nov 2014 10:13:09 GMT

Hi,

We need to convert it into date using date#() Function as shown above.

If you have some sample data please upload it.

Regards

ASHFAQ

Re: How do i split these data in a field into 2 different fields

its_anandrjs — Sun, 02 Nov 2014 10:21:39 GMT

Hi,

If in the key fields there is always 1 for first 6 months and 2 for next months then add a new fields in the load script for the first and second months

LOAD

key,

Month,

if(key=1,Month) as First6Month,

if(key=2,Month) as Second6Month

FROM

split.xlsx

(ooxml, embedded labels, table is Sheet1);

And then you get

Let me know any changes required

Regards

Anand