Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

1 Solution

Accepted Solutions
Not applicable

Re: Combine 4 numbers, problems with 'NULL'

(With help of sasi code)

Hi try this

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

16 Replies

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

Not applicable

Re: Combine 4 numbers, problems with 'NULL'

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

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

Not applicable

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 '-'.

sasiparupudi1
Honored Contributor III

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

Not applicable

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

Not applicable

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

sasiparupudi1
Honored Contributor III

Re: Combine 4 numbers, problems with 'NULL'

Try (in a table box)

Untitled2.jpg

mov
Esteemed Contributor III

Re: Combine 4 numbers, problems with 'NULL'

See if this is what you need

Not applicable

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

Community Browser