lookup() or mapping in script

[#1]

Hi guys,

how can I use the mapping or lookup functions if I have in a database ID which start with letters like:

Table1:

ID,Date,Amount

A70123,...,...,

A70124,...

A70125,...

...

A80999,...

Now I just want to make a relation the the description, which from the ID (A701*) = "OldCustomer"

and selecting all ID which start with A70 is "BestCustomer"

[tresesco · Accepted solution]

Try like:

Load

          If(WildMatch(ID, 'A701*'), 'OldCustomer', NewID) as NewID

And since A70 is superior set data, in the front end create a calculated dimension like:

=If(NewID='OldCustomer' Or WildMatch(NewID, 'A70*'), 'BestCustomer', NewID)

[#3]

or you can save the same in a variable and call the variable

[SunilChauhan]

    If(WildMatch(ID, 'A701*'), 'OldCustomer',

    If(WildMatch(ID, 'A70*'), 'Best Customers',))

but as per requirement  A701*  as old customer and A70* as Best Customer

so old customer is part best customer

you can use below

    If(WildMatch(ID, 'A701*')  , 'OldCustomer',

    If(WildMatch(ID, 'A70*') and WildMatch(ID, 'A701*') =0 , 'Best Customers'))

hope this helps

[jagan]

Hi,

Try like this in script

LOAD

*,

If(WildMatch(ID, 'A701*'), 'OldCustomer', If(WildMatch(ID, 'A70*'), 'BestCustomer', 'N/A')) AS NewDim

FROM DataSource;

Hope this helps you.

Regards,

Jagan.

[#6]

Thx much

How can I select A70100 - A70150 ?

Maybe this?: If(Wildmatch(ID, A701??) and Wildmatch(ID,A7015?), 'ImportantCust')

[tresesco]

Assuming that you have six-digit IDs preceded by a character, try like:

If( Num(Mid(ID,2))>=70100 and Num(Mid(ID,2))<=70150 , 'ImportantCust')

[SunilChauhan]

If( mid(ID,2 ,len(ID))>='70100' and mid(ID,2 ,len(ID))<='70150' , 'ImportantCust')