topic Re: Convert Excel formula into QlikView Script in QlikView

Convert Excel formula into QlikView Script

lawrance — Tue, 25 Jul 2017 06:00:41 GMT

Hi,

Please help me to convert Excel formula into QlikView Script.

IF(OR(RIGHT(G2,2)="7R",RIGHT(G2,2)="8R",RIGHT(G2,2)="8E"),CONCATENATE(IF(AL2="SINGLE",VLOOKUP(H2,'Meter Conversion Chart'!$A$3:$B$19,2,FALSE),VLOOKUP(H2,'Meter Conversion Chart'!$H$3:$I$22,2,FALSE)),"-KYZ"),IF(AL2="SINGLE",VLOOKUP(H2,'Meter Conversion Chart'!$A$3:$B$19,2,FALSE),VLOOKUP(H2,'Meter Conversion Chart'!$H$3:$I$22,2,FALSE)))

Screenshot:(Sample Data)

Thanks,

Lawrance A

Re: Convert Excel formula into QlikView Script

Anil_Babu_Samineni — Tue, 25 Jul 2017 06:48:31 GMT

May be this?

If(Match(Right(G2,2), '7R',If(Match(Right(G2,2), '8R',If(Match(Right(G2,2), '8E',

(If(AL2 = 'SINGLE', Above(ApplyMap('Meter Conversion Chart', H2),3,19), 2, 'FALSE') &

If(AL2 = 'SINGLE', Above(ApplyMap('Meter Conversion Chart', H2),3,22), 2, 'FALSE') & '-KYZ')

(If(AL2 = 'SINGLE', Above(ApplyMap('Meter Conversion Chart', H2),3,19), 2, 'FALSE') &

If(AL2 = 'SINGLE', Above(ApplyMap('Meter Conversion Chart', H2),3,22), 2, 'FALSE')))

Re: Convert Excel formula into QlikView Script

mangalsk — Tue, 25 Jul 2017 06:56:51 GMT

Can you plz share dummy data and expected result , it is not clear since there are hidden columns which you need in vlookup

Re: Convert Excel formula into QlikView Script

Anil_Babu_Samineni — Tue, 25 Jul 2017 06:59:23 GMT

To simplify use this

If(Match(Right(G2,2), '7R', '8R', '8E'),

(If(AL2 = 'SINGLE', Above(ApplyMap('Meter Conversion Chart', H2),3,19), 2, 'FALSE') &

If(AL2 = 'SINGLE', Above(ApplyMap('Meter Conversion Chart', H2),3,22), 2, 'FALSE') & '-KYZ')

(If(AL2 = 'SINGLE', Above(ApplyMap('Meter Conversion Chart', H2),3,19), 2, 'FALSE') &

If(AL2 = 'SINGLE', Above(ApplyMap('Meter Conversion Chart', H2),3,22), 2, 'FALSE')))

Re: Convert Excel formula into QlikView Script

rahulpawarb — Tue, 25 Jul 2017 07:33:29 GMT

Hello Lawrance,

Please use below sample QlikView expression:

If([Meter Phase Name] = 'SINGLE',
  ApplyMap('Map_Meter_Conversion_A', OldSize),
  ApplyMap('Map_Meter_Conversion_B', OldSize)
  )
& If(Match(Right([Equipment Model Type],2), '7R', '8R', '8E'),'-KYZ')

Note: Please create two mapping tables - Map_Meter_Conversion_A & Map_Meter_Conversion_B tables to use ApplyMap('Map_Meter_Conversion_A', OldSize) & ApplyMap('Map_Meter_Conversion_B', OldSize) functions respectively.

Regards!

Rahul Pawar