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: 
pgloc2020
Creator
Creator

Expressions in Resident Load Statement

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!

Labels (3)
1 Solution

Accepted Solutions
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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,

View solution in original post

3 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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

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

-Rob

pgloc2020
Creator
Creator
Author

Thank you so much @Oleg_Troyansky It works as needed.