move conditionally

[surajap123]

Hi Experts,

I have below source and I am expecting the output pasted in the bottom.

Source:

LOAD *

  INLINE [

  ProductList| Category| Date

  AA,BB,CC,DD,EE,FF|100|01/01/2016

  AA,BB,CC,GG|100|01/12/2015

  AA,BB,CC,DD,II|100|01/11/2015

  RR,SS,TT,UU,VV,XX|200|01/01/2016

  RR,SS,TT,LL,XX|200|01/12/2015

  RR,SS,PP,XX|200|01/11/2015

  ] (delimiter is '|')

;

The ProductList field contain, list of products separated by comma.

These Products belong to 2 groups of categories, ie 100 and 200.

My requirement is, whenever the Date is >= 01/01/2016, I want to move the product 'XX'(from ProductList field) from category 200 to 100.

Expected output-

LOAD *

  INLINE [

  ProductList| Category| Date

  AA,BB,CC,DD,EE,FF,XX|100|01/01/2016

  AA,BB,CC,GG|100|01/12/2015

  AA,BB,CC,DD,II|100|01/11/2015

  RR,SS,TT,UU,VV|200|01/01/2016

  RR,SS,TT,LL,XX|200|01/12/2015

  RR,SS,PP,XX|200|01/11/2015

  ] (delimiter is '|')

;

Please suggest.

Thanks!

[miskinmaz]

Hi,

PFA.. !

[tresesco · Accepted solution]

Try like:

Load

  Concat(ProductList, ',') as ProductList,

  Category,

  Date

  Group By Category, Date;

Load

  ProductList,

  Date,

  If( Date>='1/1/2016' and ProductList='XX' and Category=200, 100, Category) as Category;

LOAD SubField(ProductList, ',') as ProductList,

  Category,

  Date;

Load * 

  INLINE [

  ProductList| Category| Date

  AA,BB,CC,DD,EE,FF|100|01/01/2016

  AA,BB,CC,GG|100|01/12/2015

  AA,BB,CC,DD,II|100|01/11/2015

  RR,SS,TT,UU,VV,XX|200|01/01/2016

  RR,SS,TT,LL,XX|200|01/12/2015

  RR,SS,PP,XX|200|01/11/2015

  ] (delimiter is '|')

;

[surajap123]

Thanks at lot