Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
surajap123
Not applicable

move conditionally

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!

1 Solution

Accepted Solutions
tresesco
Not applicable

Re: move conditionally

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 '|')

;

3 Replies
miskinmaz
Not applicable

Re: move conditionally

Hi,

PFA.. !

tresesco
Not applicable

Re: move conditionally

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

Re: move conditionally

Thanks at lot