topic Re: Logic script in QlikView

Logic script

smilingjohn — Wed, 25 Nov 2020 16:16:04 GMT

Hi All ,

I have a table in it there is a field by name City which has some errors needs to be replaced , Below is the wildmatch iam tryong to use but i think iam wrong somewhere . I want the city name to be replaced as i have described and other than that it should display all the CITY name as it is , and iam trying to create a new field by name cit. Pleas elet me know where iam wrong , This is loading but npt giving any error .Through some light on this

City:

LOAD CITY,

if(WildMatch(CITY,'BETHLEHT*'),'BETHELEHM') ,

if(WildMatch(CITY,'ZION HI*'),'ZIONHILL') ,

if(WildMatch(CITY,'SCOTT TW*'),'SCOTT TOWNSHIP') ,

if(WildMatch(CITY,'SCHUYKL HAV*'),'SCHUYLKILL HAVEN') ,

// if(WildMatch(CITY,'S ABINGTN TWP*'),'S ABINGTON TWP'),

if(WildMatch(CITY,'NEWBUR*'),'NEWBURG'),

if(WildMatch(CITY,'NEW RING*'),'NEW RINGGOLD'),

if(WildMatch(CITY,'MT BETH*'),'MOUNT BETHEL'),

if(WildMatch(CITY,'LAURY*'),'LAURYS STATION'),

if(WildMatch(CITY,'JERY*'),'JERMYN'),

if(WildMatch(CITY,'HONEYBRO*'),'HONEY BROOK'),

if(WildMatch(CITY,'HAZLE TOWNS*'),'HAZLE TOWNSHIP'),

if(WildMatch(CITY,'EGG HBR*'),'EGG HARBOR TWP'),

if(WildMatch(CITY,'EAST STROUDSBUR*'),'EAST STROUDSBURG'),

if(WildMatch(CITY,'DOYLETOW*'),'DOYLESTOWN'),

if(WildMatch(CITY,'CHARLESTO*'),'CHARLES TOWN'),

if(WildMatch(CITY,'CATASAQ*'),'CATASAUQUA'),

if(WildMatch(CITY,'BREININGSVI*'),'BREINIGSVILLE'),

if(WildMatch(CITY,'BOYTON BE*'),'BOYNTON BEACH'),

if(WildMatch(CITY,'BOILING SPN*'),'BOILING SPRINGS'),

if(WildMatch(CITY,'SPRNG BRK*'),'SPRING BROOK TWP'),

if(WildMatch(CITY,'PHILA*'),'PHILADELPHIA'),

if(WildMatch(CITY,'S ABINGT*'),'S ABINGTON TWP'),

//if(WildMatch(CITY,'SCHUYKL HAV*'),'SCHUYLKILL HAVEN'),

if(WildMatch(CITY,'NEW PHIL*'),'NEW PHILADELPHIA'), CITY AS cit,

Re: Logic script

tamilarasu — Thu, 03 Mar 2016 05:10:48 GMT

Hi,

Use pick Wildmatch in this case.

Ex: Pick(wildmatch([Type], 'A1*', 'B1*' , 'C1*', 'D1*', 'E1*' , 'F1*'), 'A', 'B', 'C', 'D', 'E', 'F') as [Transaction Type]

Re: Logic script

PrashantSangle — Thu, 03 Mar 2016 05:15:52 GMT

Hi,

You have syntax error

modify your if statement

if(wildmatch(City,'BETHLEHT*'),'BETHELEHM',

if(wildmatch(City,'ZION HI*'),'ZIONHILL',City

)) as newCity

check closing bracket also for more details see if else in help menu

Regards

Re: Logic script

jagan — Thu, 03 Mar 2016 05:16:10 GMT

Hi,

Try like this using Pick() and WildMatch(), it is bit easier when compared to Nested IF()

=Pick(WildMatch(CITY,'BETHLEHT*', 'ZION HI*', 'SCOTT TW*'), ...........................

'BETHELEHM',

'ZIONHILL',

'SCOTT TOWNSHIP', .................................)

Hope this helps you.

Regards,

jagan.

Re: Logic script

smilingjohn — Thu, 03 Mar 2016 05:22:46 GMT

Ji jagan ,

Please find the attachment i tried the abive expression even replaced by and yet its not working

Re: Logic script

smilingjohn — Thu, 03 Mar 2016 05:23:47 GMT

Hi Tamil rajan beside this it has to pic CITY , that is else CITY

Re: Logic script

settu_periasamy — Thu, 03 Mar 2016 05:27:18 GMT

Hi John,

Try this..

Load City,

if(WildMatch(City,'BETHLEHT*'),'BETHELEHM',
if(WildMatch(City,'ZION HI*'),'ZIONHILL' ,
if(WildMatch(City,'SCOTT TW*'),'SCOTT TOWNSHIP' ,
if(WildMatch(City,'SCHUYKL HAV*'),'SCHUYLKILL HAVEN' ,
// if(WildMatch(City,'S ABINGTN TWP*'),'S ABINGTON TWP'),
if(WildMatch(City,'NEWBUR*'),'NEWBURG',
if(WildMatch(City,'NEW RING*'),'NEW RINGGOLD',
if(WildMatch(City,'MT BETH*'),'MOUNT BETHEL',
if(WildMatch(City,'LAURY*'),'LAURYS STATION',
if(WildMatch(City,'JERY*'),'JERMYN',
if(WildMatch(City,'HONEYBRO*'),'HONEY BROOK',
if(WildMatch(City,'HAZLE TOWNS*'),'HAZLE TOWNSHIP',
if(WildMatch(City,'EGG HBR*'),'EGG HARBOR TWP',
if(WildMatch(City,'EAST STROUDSBUR*'),'EAST STROUDSBURG',
if(WildMatch(City,'DOYLETOW*'),'DOYLESTOWN',
if(WildMatch(City,'CHARLESTO*'),'CHARLES TOWN',
if(WildMatch(City,'CATASAQ*'),'CATASAUQUA',
if(WildMatch(City,'BREININGSVI*'),'BREINIGSVILLE',
if(WildMatch(City,'BOYTON BE*'),'BOYNTON BEACH',
if(WildMatch(City,'BOILING SPN*'),'BOILING SPRINGS',
if(WildMatch(City,'SPRNG BRK*'),'SPRING BROOK TWP',
if(WildMatch(City,'PHILA*'),'PHILADELPHIA',
if(WildMatch(City,'S ABINGT*'),'S ABINGTON TWP',
//if(WildMatch(City,'SCHUYKL HAV*'),'SCHUYLKILL HAVEN'),
if(WildMatch(City,'NEW PHIL*'),'NEW PHILADELPHIA'))))))))))))))))))))))) as New_City

Re: Logic script

settu_periasamy — Thu, 03 Mar 2016 05:28:56 GMT

Hi,

Instead of these much if, You can use the ApplyMap..

It is easy to maintain..

Sample attached

Re: Logic script

tamilarasu — Thu, 03 Mar 2016 05:31:24 GMT

Try like below,

Pick(wildmatch([Type], 'A1*', 'B1*' , 'C1*', 'D1*', 'E1*' , 'F1*') +1 , 'A', 'B', 'C', 'D', 'E', 'F', 'No Match') as [Transaction Type]

In your case CITY (No single quotes around the CITY)

Re: Logic script

tresB — Thu, 03 Mar 2016 05:32:03 GMT

The trick of ELSE part here:

Pick(wildmatch(City, 'A1*', 'B1*' , 'C1*', 'D1*', 'E1*' , 'F1*')+1, City, 'A', 'B', 'C', 'D', 'E', 'F') as [Transaction Type]

Re: Logic script

rajeshqvd — Thu, 03 Mar 2016 05:46:32 GMT

=Pick(1+WildMatch(CITY,'BETHLEHT*', 'ZION HI*', 'SCOTT TW*'), ...........................

'BETHELEHM',

'ZIONHILL',

'SCOTT TOWNSHIP', .................................)

if its works plz close this thread

Re: Logic script

tamilarasu — Thu, 03 Mar 2016 05:47:04 GMT

Try,

Pick(Wildmatch(CITY, 'BETHLEHT*', 'ZION HI*', 'SCOTT TW*', 'SCHUYKL HAV*', 'NEWBUR*', 'NEW RING*', 'MT BETH*', 'LAURY*', 'JERY*', 'HONEYBRO*', 'HAZLE TOWNS*', 'EGG HBR*', 'EAST STROUDSBUR*', 'DOYLETOW*', 'CHARLESTO*', 'CATASAQ*', 'BREININGSVI*', 'BOYTON BE*', 'BOILING SPN*', 'SPRNG BRK*', 'PHILA*', 'S ABINGT*', 'NEW PHIL*') +1

,CITY, 'BETHELEHM, 'ZIONHILL, 'SCOTT TOWNSHIP, 'SCHUYLKILL HAVEN, 'NEWBURG, 'NEW RINGGOLD, 'MOUNT BETHEL, 'LAURYS STATION, 'JERMYN, 'HONEY BROOK, 'HAZLE TOWNSHIP, 'EGG HARBOR TWP, 'EAST STROUDSBURG, 'DOYLESTOWN, 'CHARLES TOWN, 'CATASAUQUA, 'BREINIGSVILLE, 'BOYNTON BEACH, 'BOILING SPRINGS, 'SPRING BROOK TWP, 'PHILADELPHIA, 'S ABINGTON TWP, 'NEW PHILADELPHIA') as CITTTT

I put the else part in wrong place in my last post. You can try applymap instead of this manual work. It can be easy to maintain the script.