Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Dynamic LOAD in where expression using variable

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

1 Solution

Accepted Solutions
sunny_talwar

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";

View solution in original post

9 Replies
sunny_talwar

You can do this:

variable_list:
LOAD * inline [
PO
1238962
3210028
];

LOAD PO,
Result
FROM

(ooxml, embedded labels, table is Sheet1)

Where Exists (PO);

sunny_talwar

Sample attached

Not applicable
Author

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.

sunny_talwar

‌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.

Not applicable
Author

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

Or
MVP
MVP

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

sunny_talwar

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";

Not applicable
Author

Hi Sunny,

Excellent

Interesting to see that QV environment works in SQL environment.

Thank you for your contribution!

Best regards,

Cornelis

Not applicable
Author

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