Combine 4 numbers, problems with 'NULL'

[#1]

Hi all,

I have to combine 4 Numbers in the following way:

WG1	WG2	WG3	WG4	WGR
1	1	2	5	1-1-2-5
2	3	6	6	2-3-6-6
1	7			1-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

[Anonymous]

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

[#3]

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

[sasiparupudi1]

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,,,

,,,,

];

[Anonymous]

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

[#6]

It works for this data

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

O/P:

Good work Sasi sir

[#7 · Accepted solution]

(With help of sasi code)

Hi try this

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

[d_koti24]

Hi Philipp,

it may helps you:

SAMPLE:

LOAD * INLINE [

    A, B, C

    1, 2, 3

    3, , 4

    4, 4

    , 3, 3

    5, 6, 7

];

RES:

LOAD *,

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