Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Convert Excel formula into QlikView Script

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)

PIC_01.png

PIC_02.png

Thanks,

Lawrance A

4 Replies
Highlighted

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Creator III
Creator III

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

Highlighted

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Specialist III
Specialist III

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