Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Lookup base on condition of another table

BS_MAP:

LOAD

GL_BS_MAP as GL_BS_MAP

FROM


FERMAT:

LOAD

JOURNAL_ENTRY

IF(LEN(GL_BS_MAP)='5', LOOKUP('LEVEL0', 'GL_BS_MAP', mid(JOURNAL_ENTRY, 9, 5), 'BS_MAP'), LOOKUP('LEVEL0', 'GL_BS_MAP', mid(JOURNAL_ENTRY, 9, 9), 'BS_MAP')) AS LEVEL0

FROM

[\\Fssg51\fsp13\Corporate Treasury Projects\CT-SG\QVD\QLIKMART\FERMAT\L2_FERMAT.QVD]

How can I do this mapping? GL_BS_MAP can be in the format of 11111 or 11111-222. JOURNAL_ENTRY is in the format of 111-222-33333-444. Therefore, if GL_BS_MAP has a length of 5, I want to use LOOKUP('LEVEL0', 'GL_BS_MAP', mid(JOURNAL_ENTRY, 9, 5), 'BS_MAP') for my mapping, but if GL_BS_MAP has a length of 9, I want to use LOOKUP('LEVEL0', 'GL_BS_MAP', mid(JOURNAL_ENTRY, 9, 9), 'BS_MAP'). However, Qlikview prevents me from even checking the len of GL_BS_MAP because it is not in the FERMAT table. How can I go about doing this? Thank you

0 Replies