Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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"

Tags (3)
1 Solution

Accepted Solutions
MVP
MVP

Re: lookup() or mapping in script

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)

7 Replies
MVP
MVP

Re: lookup() or mapping in script

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

Re: lookup() or mapping in script

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

chauhans85
Esteemed Contributor

Re: lookup() or mapping in script

    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




MVP
MVP

Re: lookup() or mapping in script

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

Re: lookup() or mapping in script

Thx much

How can I select A70100 - A70150 ?

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

MVP
MVP

Re: lookup() or mapping in script

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')

chauhans85
Esteemed Contributor

Re: lookup() or mapping in script

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

Community Browser