Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

lookup() or mapping in script

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"

1 Solution

Accepted Solutions
tresesco
MVP
MVP

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)

View solution in original post

7 Replies
tresesco
MVP
MVP

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)

Not applicable
Author

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

SunilChauhan
Champion
Champion

    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




Sunil Chauhan
jagan
Luminary Alumni
Luminary Alumni

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.

Not applicable
Author

Thx much

How can I select A70100 - A70150 ?

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

tresesco
MVP
MVP

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
Champion
Champion

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

Sunil Chauhan