Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

honeyparpay
New Contributor III

Create a string on LOAD

Hi there!

I need some help with this.

Is it possible to create a string concatenating the values of the items 1 to 5 by ID as shown bellow?

IDITEM ORDERVALUESTRING
20155751222222
201557522
201557532
201557542
201557552
2015575620-04-2014
61523581111211
615235821
615235832
615235841
615235851
6152358602-01-2013
61545121111111
615451221
615451231
615451241
615451251
6154512606-03-2014

Regards!

Tags (3)
1 Solution

Accepted Solutions

Re: Create a string on LOAD

In LOAD Script it becomes very simple:

Data:

LOAD ID, [ITEM ORDER], VALUE, [EXAMPLE STRING], ID & [ITEM ORDER] AS Key INLINE [

ID, ITEM ORDER, VALUE, EXAMPLE STRING

2015575,1,2,22222

2015575,2,2,

2015575,3,2,

2015575,4,2,

2015575,5,2,

2015575,6,20-04-2014,

6152358,1,1,11211

6152358,2,1,

6152358,3,2,

6152358,4,1,

6152358,5,1,

6152358,6,02-01-2013,

6154512,1,1,11111

6154512,2,1,

6154512,3,1,

6154512,4,1,

6154512,5,1,

6154512,6,06-03-2014,

];

LEFT JOIN (Data) LOAD ID & '1' AS Key, Concat(VALUE, '', [ITEM ORDER]) AS STRING GROUP BY ID;

NOCONCATENATE LOAD * RESIDENT Data WHERE [ITEM ORDER] < 6;

DROP Field Key;

See attached doc.

Best,

Peter

8 Replies

Re: Create a string on LOAD

Use Concat function ...

Highlighted
anbu1984
Honored Contributor III

Re: Create a string on LOAD

Load *,RecNo() As Rn Inline [

ID,ITEM_ORDER,VALUE

2015575,1,2

2015575,2,2

2015575,3,2

2015575,4,2

2015575,5,2

2015575,6,20-04-2014

6152358,1,1

6152358,2,1

6152358,3,2

6152358,4,1

6152358,5,1

6152358,6,02-01-2013

6154512,1,1

6154512,2,1

6154512,3,1

6154512,4,1

6154512,5,1

6154512,6,06-03-2014];

Join

Load ID, Concat(If(Index(VALUE,'-')=0,VALUE),'',Rn) As String, Min(Rn) As Rn Resident Initial Group by ID;

Join

Load ID, Concat(If(Len(VALUE)=1,VALUE),'',Rn) As String, Min(Rn) As Rn Resident Initial Group by ID;

Re: Create a string on LOAD

Some expression like:

=IF (ITEM = 1, aggr(concat({<[ITEM ORDER] = {'>=1<6'}>} VALUE), ID))

anbu1984
Honored Contributor III

Re: Create a string on LOAD

To maintain order of Value

=IF (ITEM_ORDER = 1, aggr(concat({<[ITEM_ORDER] = {'>=1<6'}>} VALUE,'',ITEM_ORDER), ID))

anbu1984
Honored Contributor III

Re: Create a string on LOAD

Load * Inline [

ID,ITEM_ORDER,VALUE

2015575,1,2

2015575,2,2

2015575,3,2

2015575,4,2

2015575,5,2

2015575,6,20-04-2014

6152358,1,1

6152358,2,1

6152358,3,2

6152358,4,1

6152358,5,1

6152358,6,02-01-2013

6154512,1,1

6154512,2,1

6154512,3,1

6154512,4,1

6154512,5,1

6154512,6,06-03-2014];

Join

Load ID, Concat(If(Index(VALUE,'-')=0,VALUE),'',ITEM_ORDER) As String, Min(ITEM_ORDER) As ITEM_ORDER Resident Initial Group by ID;

Re: Create a string on LOAD

In LOAD Script it becomes very simple:

Data:

LOAD ID, [ITEM ORDER], VALUE, [EXAMPLE STRING], ID & [ITEM ORDER] AS Key INLINE [

ID, ITEM ORDER, VALUE, EXAMPLE STRING

2015575,1,2,22222

2015575,2,2,

2015575,3,2,

2015575,4,2,

2015575,5,2,

2015575,6,20-04-2014,

6152358,1,1,11211

6152358,2,1,

6152358,3,2,

6152358,4,1,

6152358,5,1,

6152358,6,02-01-2013,

6154512,1,1,11111

6154512,2,1,

6154512,3,1,

6154512,4,1,

6154512,5,1,

6154512,6,06-03-2014,

];

LEFT JOIN (Data) LOAD ID & '1' AS Key, Concat(VALUE, '', [ITEM ORDER]) AS STRING GROUP BY ID;

NOCONCATENATE LOAD * RESIDENT Data WHERE [ITEM ORDER] < 6;

DROP Field Key;

See attached doc.

Best,

Peter

honeyparpay
New Contributor III

Re: Create a string on LOAD

Thanks a lot Peter!

honeyparpay
New Contributor III

Re: Create a string on LOAD

Thank you for your time, Ambu.

Both of you (Peter and you) made a good job helping me.

Regards,

Lander