topic Re: how to concat values? in QlikView

how to concat values?

Wed, 07 May 2014 08:55:02 GMT

this is the box i have:

however, this is the desired output:

So, how to do it?

Re: how to concat values?

CELAMBARASAN — Wed, 07 May 2014 09:03:09 GMT

Use SubField(FieldName, ',')

Try it in Script, not as expression in chart

Re: how to concat values?

its_anandrjs — Wed, 07 May 2014 09:05:21 GMT

Add new field in the load like

SubField(YourFieldNameHere, ',') as NewField

Re: how to concat values?

sudeepkm — Wed, 07 May 2014 09:07:57 GMT

Do you just want to show the three categories ignoring others?

you want to merge the other categories to the major 3 categories?

Re: how to concat values?

its_anandrjs — Wed, 07 May 2014 09:09:06 GMT

Load your table like

Source:

LOAD * Inline

[

String

'a,b'

'c,d'

'p,q'

];

if you want after ' then use SubField(String,',',1) otherwise use SubField(String,',')

LOAD SubField(String,',') as NewField1, SubField(String,',',1) as NewField2;

LOAD * Inline

[

String

'a,b'

'c,d'

'p,q'

];

Re: how to concat values?

Wed, 07 May 2014 09:11:33 GMT

i want to merge the other categories to the major 3 categories, however, for example"user request, recovery" is combine together how to separate them and add in accordingly?

Re: Re: how to concat values?

Wed, 07 May 2014 09:14:24 GMT

PFA..

Hope this helps you

Re: how to concat values?

sudeepkm — Wed, 07 May 2014 09:17:40 GMT

Assume that you have a record with RecID = 11 which has "Request, Recovery" in the table:

RecID - Purpose

11 - Request,Recovery

then do you want two separate records to be created like below

RecID - Purpose

11 - Request

11 - Recovery

Re: how to concat values?

blaise — Wed, 07 May 2014 09:19:21 GMT

I guess the numbers to the right are the frequency?

If you want to split "User Request, Recovery" with a freq. of 8, how many of those do you want for "user request" and how many for "Implementation"? Any other field in the datamodel that can answer that question?

If you do not care about the frequency i would suggest to create a new field in your datamodel using the subfield() function.

split:

load

[Purpose_of_Request/fieldvalue],

subfield([Purpose_of_Request/fieldvalue],',') as splitField

resident

tablename

;

subfield() without the "index" parameter is quite useful for cases like this.

Re: how to concat values?

Wed, 07 May 2014 09:24:02 GMT

yes, after separate them then add in to the main category. but the number of request and recovery may not be the same it according to the data i have.

Re: how to concat values?

Wed, 07 May 2014 09:28:04 GMT

this is the script i have:

I just add" subfield([Purpose_of_Request/fieldvalue],',') as splitField" ?

Re: how to concat values?

anbu1984 — Wed, 07 May 2014 09:31:17 GMT

Yes

Re: how to concat values?

blaise — Wed, 07 May 2014 09:32:14 GMT

I would rather do it in a new table (to avoid duplicating the data) as seen in my code snippet.

Do you know how to user the resident load?

f.ex.

Table:

load

field1,

field2

from

table.qvd

(qvd)

;

Table2:

load

field1,

subfield(field1,',') as field1split

resident

Table

;

Resident loads records from an already loaded table.

Re: how to concat values?

Wed, 07 May 2014 09:38:44 GMT

hii,

when ever you want to split a string after a symbol (, . - _ ) then you can use the subfield() function.

In your problem,

you can use it like subfield('Purpose_of_Request',',').

Hopefully it will work.

Thanks

Re: how to concat values?

Wed, 07 May 2014 09:39:26 GMT

subfield() will split the Purpose_of_Request after the comma.

Re: Re: how to concat values?

sudeepkm — Wed, 07 May 2014 10:00:17 GMT

Please take a look at the attachment. whenever there is a record with two Purpose values like Request,Recovery

in that case two records will be created one with Request and another with Recovery.

Finally there will be only three categories.

Re: how to concat values?

Thu, 08 May 2014 01:44:16 GMT

field1 and field 2 means "recovery and implementation"?