Convert Excel formula into QlikView Script

[lawrance]

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

[Anil_Babu_Samineni]

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

[mangalsk]

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

[Anil_Babu_Samineni]

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

[rahulpawarb]

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