Expressions in Resident Load Statement

[pgloc2020]

Hello all,

This is the first time I am trying to create a Resident Load statement and seeking some help-

Data is coming from 3 different tables. Table -1, Table 2, Table 3

It looks something like this in Load Statement-

[Table-1]:

LOAD
[ID],
[Queue] ,
[CLOSE_DT] ,
[REG_TYPE] ,
[SUBTYPE]
FROM [/Table-1.qvd]
(qvd);

[Table-2]:
LOAD
[ROW_ID] as [ID],
[LOGIN]
FROM [/Table-2.qvd]
(qvd);

[Table-3]:
LOAD

[ROW_ID] as [ID],
FORM_TYPE,
APPL_TYPE

FROM [/Table-3.qvd]
(qvd);

 Now I need to add an additional column MatchKey using the following formula (it is used in excel)-

[SUBTYPE]&(If([Queue]='MPREGQ','MPREGQ', If([Queue]='MPREGQE', 'MPREGQE', 'any')))&(If([LOGIN]='MPREGQ','MPREGQ',
If([LOGIN]='MPREGQE', 'MPREGQE', 'any')))&[FORM_TYPE]&[APPL_FORM]&[REG_TYPE] AS [MatchKey]

I am assuming this will be doable using Resident load. I tried many ways to do that but all is I am getting error.

Please let me know how I can use the above formula in Qlik Load statement.

Thanks!

[Oleg_Troyansky · Accepted solution]

Hi there,

It looks like your formula contains fields from all 3 tables. In order to process something like this, you'd need to bring all this data into a single table. By looking at your data, I am guessing that you need to JOIN  the data from tables 2 and 3 into table 1. Once you have all the data in one table, you will be able to reload it again using a RESIDENT load, and your formula should work then.

So, in a nutshell, your script should look like this:

[Table-1]:

LOAD
[ID],
[Queue] ,
[CLOSE_DT] ,
[REG_TYPE] ,
[SUBTYPE]
FROM [/Table-1.qvd]
(qvd);

//[Table-2]:

LEFT JOIN ([Table-1])
LOAD
[ROW_ID] as [ID],
[LOGIN]
FROM [/Table-2.qvd]
(qvd);

//[Table-3]:

LEFT JOIN ([Table-1])
LOAD

[ROW_ID] as [ID],
FORM_TYPE,
APPL_TYPE

FROM [/Table-3.qvd]
(qvd);

FinalTable:
LOAD
*,
[SUBTYPE]&(If([Queue]='MPREGQ','MPREGQ', If([Queue]='MPREGQE', 'MPREGQE', 'any')))&(If([LOGIN]='MPREGQ','MPREGQ',
If([LOGIN]='MPREGQE', 'MPREGQE', 'any')))&[FORM_TYPE]&[APPL_FORM]&[REG_TYPE] AS [MatchKey]
resident [Table-1]
;

drop table [Table-1];

--------------------------------
Cheers,

[rwunderlich]

In addition to @Oleg_Troyansky excellent suggestion, see here for some more options. 

https://qlikviewcookbook.com/2020/03/creating-temporary-script-associations/

-Rob

[pgloc2020]

Thank you so much @Oleg_Troyansky It works as needed.