topic Re: Combine 4 numbers, problems with 'NULL' in QlikView

Combine 4 numbers, problems with 'NULL'

Thu, 20 Aug 2015 09:48:36 GMT

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

Re: Combine 4 numbers, problems with 'NULL'

Kushal_Chawda — Thu, 20 Aug 2015 09:57:09 GMT

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'

Thu, 20 Aug 2015 10:06:52 GMT

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

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

Re: Combine 4 numbers, problems with 'NULL'

Thu, 20 Aug 2015 10:08:42 GMT

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'

sasiparupudi1 — Thu, 20 Aug 2015 10:40:12 GMT

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'

Thu, 20 Aug 2015 10:47:31 GMT

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'

Thu, 20 Aug 2015 10:51:31 GMT

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'

sasiparupudi1 — Thu, 20 Aug 2015 10:51:47 GMT

Try (in a table box)

Re: Combine 4 numbers, problems with 'NULL'

Anonymous — Thu, 20 Aug 2015 11:05:14 GMT

See if this is what you need

Re: Combine 4 numbers, problems with 'NULL'

Thu, 20 Aug 2015 11:18:39 GMT

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'

Anonymous — Thu, 20 Aug 2015 11:23:55 GMT

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

Re: Combine 4 numbers, problems with 'NULL'

Thu, 20 Aug 2015 11:38:50 GMT

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

Re: Combine 4 numbers, problems with 'NULL'

sasiparupudi1 — Thu, 20 Aug 2015 11:39:14 GMT

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'

Anonymous — Thu, 20 Aug 2015 11:54:55 GMT

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'

Thu, 20 Aug 2015 12:01:21 GMT

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'

Thu, 20 Aug 2015 12:07:07 GMT

(With help of sasi code)

Hi try this

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

Re: Combine 4 numbers, problems with 'NULL'

d_koti24 — Thu, 20 Aug 2015 12:08:39 GMT

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,