help With String function

[arusanah]

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)

[Anonymous]

See attached

[arusanah]

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.

[danieloberbilli]

something like that?

please find attached the .qvw

[arusanah]

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;

[danieloberbilli]

you have to join the tables

[danieloberbilli]

[arusanah]

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

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

[danieloberbilli]

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

[arusanah]

this is the output i am getting ,

row2 column shows null value