topic Re: help with expression in QlikView

help with expression

arusanah — Sun, 31 May 2015 06:42:04 GMT

In attached xls, i have two tabs

tab1 : first data source

Payer

SORELEC (4758741)

SOVE ()

Process & Plant Sales Ltd ()

PRODEX NORTH EUROPEAN (177635)

ABS-5006 (67548909)

FINCH-020 (566365657)

Tab 2:

Payer	Payer number
SORELEC	4758741
PRODEX NORTH EUROPEAN	177635
ABS-5006	67548909
FINCH-020	566365657

My requirement is to have one table where i have flag assigned to each record as

Payer	Payer number	Pflag
SORELEC	4758741	1
PRODEX NORTH EUROPEAN	177635	1
ABS-5006	67548909	1
FINCH-020	566365657	1

i tried the following expression num(trim(TextBetween(Payer.Name,'(',')',substringcount(Payer.Name,'(')))) but in my result table i am losing my two customers

SOVE ()

Process & Plant Sales Ltd ()

My mind has stopped working .. please help !!

Re: help with expression

petter — Sun, 31 May 2015 09:20:59 GMT

How are you "losing" your customers? Are you making a single table in your load script and then only end up with four rows. You say that you want four rows in your requirements - so I am not sure what you are asking for...

But let give you a hint about the TextBetween() - it will return an empty string so if you check for an empty string you could replace the value with a special number like -1 or a running number that is negative for those that dont' have a Payer number... then you can retain all Payers even if they don't have a payer number.

What you do should be based on some clear requirements.

Here is an approach that might work for you:

Payer:

LOAD

PayerName1,

If ( [Payer number] = '' , -AutoNumber(RowNo(),'Payer Number') , [Payer number] ) AS [Payer number]

;

LOAD

SubField(Payer1,'(',1) AS PayerName1

, TextBetween(Payer1,'(',')') AS [Payer number]

INLINE [

Payer1

SORELEC (4758741)

SOVE ()

Process & Plant Sales Ltd ()

PRODEX NORTH EUROPEAN (177635)

ABS-5006 (67548909)

FINCH-020 (566365657)

] (delimiter is \t);

Payer2:

LOAD * INLINE [

Payer Payer number

SORELEC 4758741

PRODEX NORTH EUROPEAN 177635

ABS-5006 67548909

FINCH-020 566365657

] (delimiter is \t);

Re: help with expression

awhitfield — Mon, 01 Jun 2015 11:26:13 GMT

Hi there,

should there be an excel attachment on this post?

Andy