topic Re: lookup() or mapping in script in QlikView

lookup() or mapping in script

Tue, 24 Jun 2014 08:29:47 GMT

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"

Re: lookup() or mapping in script

tresB — Tue, 24 Jun 2014 08:33:34 GMT

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)

Re: lookup() or mapping in script

Tue, 24 Jun 2014 08:36:51 GMT

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

Re: lookup() or mapping in script

SunilChauhan — Tue, 24 Jun 2014 08:40:10 GMT

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

Re: lookup() or mapping in script

jagan — Tue, 24 Jun 2014 09:01:04 GMT

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.

Re: lookup() or mapping in script

Tue, 24 Jun 2014 09:47:12 GMT

Thx much

How can I select A70100 - A70150 ?

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

Re: lookup() or mapping in script

tresB — Tue, 24 Jun 2014 09:53:39 GMT

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

Re: lookup() or mapping in script

SunilChauhan — Tue, 24 Jun 2014 10:46:35 GMT

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