16 Replies Latest reply: Aug 20, 2015 8:08 AM by koti d

# Combine 4 numbers, problems with 'NULL'

Hi all,

I have to combine 4 Numbers in the following way:

WG1WG2WG3WG4WGR
11251-1-2-5
23662-3-6-6
171-7- -

I would like to tell QlikView to combine the fields like this

WG1 & '-' & WG2 & '-' & WG3 & '-' WG4'

The Problem is, in this case I recieved a Result like in the last line of the table.

Then I tried some combinations with if, like this:

if(wg1>0,wg1,)&if(wg2>0,'-'&wg2,)&if(wg3>0,'-'&wg3,)&if(wg4>0,'-'&wg4,) as WGR

But the result is also not correct in every line.

I was thinking if there is a possibility to setup the 'Else' in the 'If' condition to NULL like this:

if(wg1>0,wg1,Null)&if(wg2>0,'-'&wg2,Null)&if(wg3>0,'-'&wg3,Null)&if(wg4>0,'-'&wg4,Null) as WGR

But of course in this way it do not work.

It would be so nice if anybody can help me with that

• ###### Re: Combine 4 numbers, problems with 'NULL'

try this

if(len(trim(WG1))>0,WG1,'') &'-'&

if(len(trim(WG2))>0,WG2,'') &'-'&

if(len(trim(WG3))>0,WG3,'') &'-'&

if(len(trim(WG4))>0,WG4,'')

• ###### Re: Combine 4 numbers, problems with 'NULL'

WG1&'-'&WG2&'-'&WG3&'-'&WG4 as WGR

Take this WGR Field ...I hope this result only you want

• ###### Re: Combine 4 numbers, problems with 'NULL'

Hi,

I think there is also the Problem that QV will create the '-' even if forexample WG1 do not exist. The result is something like that ' - 3 -2 -'

But I just want to have necessary '-'.

• ###### Re: Combine 4 numbers, problems with 'NULL'

if(len(trim(WG1))>0 and len(trim(WG2))>0 and len(trim(WG3))>0 and len(trim(WG4))>0,WG1&'-'&WG2&'-'&WG3&'-'&WG4) as WGR

• ###### Re: Combine 4 numbers, problems with 'NULL'

Hi

if(len(trim(WG1))>0 and len(trim(WG2))>0 and len(trim(WG3))>0 and len(trim(WG4))>0,WG1&'-'&WG2&'-'&WG3&'-'&WG4) as WGR

This gave the below result, null row missed

• ###### Re: Combine 4 numbers, problems with 'NULL'

Try (in a table box)

• ###### Re: Combine 4 numbers, problems with 'NULL'

See if this is what you need

• ###### Re: Combine 4 numbers, problems with 'NULL'

Hi Try This

if(len(trim(WG1))>0 and len(trim(WG2))>0 and len(trim(WG3))>0 and len(trim(WG4))>0,WG1&'-'&WG2&'-'&WG3&'-'&WG4,

if(len(trim(WG1))>0 and len(trim(WG2))>0 and len(trim(WG3))>0, WG1&'-'&WG2&'-'&WG3,

if(len(trim(WG1))>0 and len(trim(WG2))>0, WG1&'-'&WG2,

if(len(trim(WG1))>0, WG1)))) as WGR

This will Give below result

• ###### Re: Combine 4 numbers, problems with 'NULL'

Good Idea, but this case helps just in a hirachie case... what happens if, wg1 exist wg2 exist wg4 exist but wg3 is NULL... I think then this do not work

• ###### Re: Combine 4 numbers, problems with 'NULL'

What is the result you are expecting from the last row of your example data?

• ###### Re: Combine 4 numbers, problems with 'NULL'

The Problem is, that it is possible that the completeness of the data is mixed like this:

• ###### Re: Combine 4 numbers, problems with 'NULL'

Try this;

Replace(rtrim(ltrim(if(len(trim(WG1))>0,WG1) &' '& if(len(trim(WG2))>0,WG2) &' '& if(len(trim(WG3))>0,WG3) &' '& if(len(trim(WG4))>0,WG4))), ' ', '-') AS Fieldname

I have not tested it, but I think it should work. And, it might not be the best way to do it, but still, it should work

• ###### Re: Combine 4 numbers, problems with 'NULL'

Try

load WG1,WG2,WG3,WG4,replace(trim(WG1&' '&WG2&' '&WG3&' '&WG4),' ','-')  as WGR

inline [

WG1,WG2,WG3,WG4

1,1,2,5

2,3,6,6

1,7,,,

,,,,

];

• ###### Re: Combine 4 numbers, problems with 'NULL'

It works for this data

But, it gives extra -- when the starting values are null

O/P:

Good work Sasi sir

• ###### Re: Combine 4 numbers, problems with 'NULL'

(With help of sasi code)

Hi try this

Replace(replace(trim(WG1&' '&WG2&' '&WG3&' '&WG4),' ','-'),'--','-')  as WGR

• ###### Re: Combine 4 numbers, problems with 'NULL'

Hi Philipp,

it may helps you:

SAMPLE:

A, B, C

1, 2, 3

3, , 4

4, 4

, 3, 3

5, 6, 7

];

RES:

A&'_'&B&'_'&C AS ABC,

IF(LEN(A)>0 AND LEN(B)>0 AND LEN(C)>0,A&'_'&B&'_'&C,

IF(LEN(A)>0 AND LEN(B)>0 AND LEN(C)=0,A&'_'&B,

IF(LEN(A)>0 AND LEN(B)=0 AND LEN(C)>0 ,A&'_'&C,

IF(LEN(A)=0 AND LEN(B)>0 AND LEN(C)>0,B&'_'&C ))))AS ABC2

Resident SAMPLE;

DROP Table SAMPLE;

regards,

kd