Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
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)

View solution in original post

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)

View solution in original post

Not applicable

Re: lookup() or mapping in script

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

SunilChauhan
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 & Luminary
MVP & Luminary

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

SunilChauhan
Esteemed Contributor

Re: lookup() or mapping in script

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