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

[#1]

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

[ashfaq_haseeb]

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

[#3]

Thanks Ashfaq

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

[ashfaq_haseeb]

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

[its_anandrjs · Accepted solution]

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