topic Re: help With String function in QlikView

help With String function

arusanah — Thu, 21 May 2015 23:57:48 GMT

I still need help with one of my requirement.

I have data coming from a database -- which i have in sheet2 of sample excel attached.

sheet1 has the data thatz in another source. sheet3 is the output i want to see after .

the requirement is : as soon as data is added to sheet2 is should cheek in sheet 1 if it exists or not .. if not set flag to 1 else 0.

tried couple of solution but nothing works . ( you can find it all my possible in multiple tabs i Qvw File)

Re: help With String function

Anonymous — Fri, 22 May 2015 00:19:00 GMT

See attached

Re: help With String function

arusanah — Fri, 22 May 2015 02:51:16 GMT

in above excel data in sheet2 is within the quotation mark

its not length of the customer that is point of concern . it is quotation mark that we need to get rid of .

i tried textbetween() & purgechar() , it work on my sample data but not when I write script in my actual data.

Re: help With String function

danieloberbilli — Fri, 22 May 2015 03:32:58 GMT

something like that?

please find attached the .qvw

Re: help With String function

arusanah — Fri, 22 May 2015 04:15:57 GMT

hi Daniel ,

when i try to put your login into my real data it fails.

here is the script . pls help me find what i'm doing wrong :

temp:

LOAD distinct Payer as Payer.Name,

[Discount Area],

1 as row1

// If(Len(Trim(Customer)) = 0, Null(), 0) as PayerFlag

FROM

C:\Users\rakesh\Desktop\test\IDS_EMEA_Discount_Customer.xlsx

(ooxml, embedded labels, header is 1 lines, table is [DISTRIBUTOR DETAILS]);

//Join(temp)

PayerTable:

LOAD distinct PurgeChar(Payer.Name,chr(39))&'('& Payer.Number &')' as Payer.Name,

1 as row2

FROM

C:\Users\rakesh\Desktop\test\p1.qvd(qvd);

Final:

LOAD *,

if(row2 = 1 and IsNull(row1), 1,0) as PayerFlag

;

LOAD Payer.Name ,row1,row2

Resident temp;

//DROP Table temp;

Re: help With String function

danieloberbilli — Fri, 22 May 2015 04:20:18 GMT

you have to join the tables

Re: help With String function

danieloberbilli — Fri, 22 May 2015 04:21:59 GMT

Re: help With String function

arusanah — Fri, 22 May 2015 04:28:13 GMT

even after joining I'm getting same output Row2 goes blank ...

do you think it could be bcoz of the "distinct " load

Re: help With String function

danieloberbilli — Fri, 22 May 2015 04:35:43 GMT

could you describe, upload or do a screenshot of the table viewer whats happening exactly?

Re: help With String function

arusanah — Fri, 22 May 2015 04:46:26 GMT

this is the output i am getting ,

row2 column shows null value

Re: help With String function

danieloberbilli — Fri, 22 May 2015 04:59:12 GMT

is it null in every row?

Re: help With String function

danieloberbilli — Fri, 22 May 2015 04:59:27 GMT

in every row of the field row2?

Re: help With String function

arusanah — Fri, 22 May 2015 05:00:00 GMT

yes

Re: help With String function

Fri, 22 May 2015 05:25:45 GMT

Why don't you just replace the quotation marks with blank?