topic Re: Dynamic LOAD in where expression using variable in QlikView

Dynamic LOAD in where expression using variable

Wed, 25 Nov 2020 16:16:04 GMT

Dear all,

see here a simple script:

variable_list:
LOAD * inline
[
PO
1238962
3210028
]
;

Directory;
LOAD PO,
Result
FROM

(ooxml, embedded labels, table is Sheet1) where match(PO, 1238962, 3210028);

The excel file has two columns: PO and Results.

Loading of this excel file into QV can be done under the restriction that only filed PO must have numbers 1238962, 3210028.

The situation becomes differently when the filled PO changes dynamically depending on the size of data.

Table "variable_list" contains the information which records must be picked up from table Directory (so the excel file "example.xlsx").

I suppose you need a macro so that you can change the where statement dynamically.

Anny idea how to solve this?

Best regards,

Cornelis

Re: Dynamic LOAD in where expression using variable

sunny_talwar — Mon, 12 Sep 2016 12:33:08 GMT

You can do this:

variable_list:
LOAD * inline [
PO
1238962
3210028
];

LOAD PO,
Result
FROM

(ooxml, embedded labels, table is Sheet1)

Where Exists (PO);

Re: Dynamic LOAD in where expression using variable

sunny_talwar — Mon, 12 Sep 2016 12:35:00 GMT

Sample attached

Re: Dynamic LOAD in where expression using variable

Mon, 12 Sep 2016 12:46:03 GMT

Hi Sunny,

Yes, this works for excel as demonstrated above.

However, it does not work for ODBC:

Variable_list:
LOAD * inline
[
PO
1149738
]
;

ODBC CONNECT TO QGSRO (UserId is QGSRO, Password is QGSRO);
QC:
LOAD BatchID, RegistrationDate, AnalysisDescription, AnalysisResult
WHERE Operation = '0020' and BatchID Exists (PO)
SELECT
BatchID,
Operation,
RegistrationDate,
AnalysisDescription,
AnalysisResult
FROM "LABSQManalysis";

Do you know how to handle this?

Best regards,

Cornelis.

Re: Dynamic LOAD in where expression using variable

sunny_talwar — Mon, 12 Sep 2016 12:55:17 GMT

‌Try this

Variable_list:
LOAD * inline
[
PO
1149738
]
;

ODBC CONNECT TO QGSRO (UserId is QGSRO, Password is QGSRO);
QC:
LOAD BatchID, RegistrationDate, AnalysisDescription, AnalysisResult
WHERE Operation = '0020' and Exists (PO, BatchID)
SELECT
BatchID,
Operation,
RegistrationDate,
AnalysisDescription,
AnalysisResult
FROM "LABSQManalysis";

Do you know how to handle this?

Best regards,

Cornelis.

Re: Dynamic LOAD in where expression using variable

Mon, 12 Sep 2016 13:04:26 GMT

Hi Sunny,

Unfortunately not, but it is a good attemp.

ODBC CONNECT TO QGSRO (UserId is QGSRO, Password is QGSRO);
QC:
LOAD BatchID, RegistrationDate, AnalysisDescription, AnalysisResult
WHERE Operation = '0020' and Exists (PO, BatchID)
SELECT
BatchID,
Operation,
RegistrationDate,
AnalysisDescription,
AnalysisResult
FROM "LABSQManalysis";

I receive "garbage error".

The problem is taht QV script does not accept

SELECT
BatchID,
Operation,
RegistrationDate,
AnalysisDescription,
AnalysisResult
FROM "LABSQManalysis";

... all are underscored with a red line, indicating a programming error.

Best regards,

Cornelis

Re: Dynamic LOAD in where expression using variable

Or — Mon, 12 Sep 2016 13:10:48 GMT

I feel like I'm missing something here. Why not simply:

variable_list:

LOAD * inline
[
PO
1238962
3210028
]
;

Directory;

Left Join(variable_list)
LOAD PO,
Result
FROM

(ooxml, embedded labels, table is Sheet1);

Re: Dynamic LOAD in where expression using variable

sunny_talwar — Mon, 12 Sep 2016 13:30:54 GMT

My bad, I missed a semi-colon

Variable_list:
LOAD * inline
[
PO

1149738
];

ODBC CONNECT TO QGSRO (UserId is QGSRO, Password is QGSRO);
QC:
LOAD BatchID, RegistrationDate, AnalysisDescription, AnalysisResult
WHERE Operation = '0020' and Exists (PO, BatchID);
SELECT
BatchID,
Operation,
RegistrationDate,
AnalysisDescription,
AnalysisResult
FROM "LABSQManalysis";

Re: Dynamic LOAD in where expression using variable

Mon, 12 Sep 2016 13:59:55 GMT

Hi Sunny,

Excellent

Interesting to see that QV environment works in SQL environment.

Thank you for your contribution!

Best regards,

Cornelis

Re: Dynamic LOAD in where expression using variable

Tue, 13 Sep 2016 06:31:55 GMT

Dear Or shoman,

That is correct, this solution is possible.

However, it does not work for ODBC it contains huge amount of data and I need a where expression to filter.

Thank you for your contribution, this is something interesting for the near future.

Best regards,

Cornelis