topic Re: Create a string on LOAD in QlikView

Create a string on LOAD

Anonymous — Fri, 10 Oct 2014 12:17:35 GMT

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?

ID	ITEM ORDER	VALUE	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

Regards!

Re: Create a string on LOAD

alexandros17 — Fri, 10 Oct 2014 12:20:18 GMT

Use Concat function ...

Re: Create a string on LOAD

anbu1984 — Fri, 10 Oct 2014 12:25:03 GMT

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

Peter_Cammaert — Fri, 10 Oct 2014 12:27:37 GMT

Some expression like:

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

Re: Create a string on LOAD

anbu1984 — Fri, 10 Oct 2014 12:34:43 GMT

To maintain order of Value

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

Re: Create a string on LOAD

anbu1984 — Fri, 10 Oct 2014 12:39:45 GMT

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

Peter_Cammaert — Fri, 10 Oct 2014 12:49:46 GMT

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

Re: Create a string on LOAD

Anonymous — Fri, 10 Oct 2014 12:56:58 GMT

Thanks a lot Peter!

Re: Create a string on LOAD

Anonymous — Fri, 10 Oct 2014 13:01:38 GMT

Thank you for your time, Ambu.

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

Regards,

Lander