Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
see here a simple script:
variable_list:
LOAD * inline
[
PO
1238962
3210028
]
;
Directory;
LOAD PO,
Result
FROM
(
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
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";
You can do this:
variable_list:
LOAD * inline [
PO
1238962
3210028
];
LOAD PO,
Result
FROM
(ooxml, embedded labels, table is Sheet1)
Where Exists (PO);
Sample attached
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.
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.
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
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
(
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";
Hi Sunny,
Excellent
Interesting to see that QV environment works in SQL environment.
Thank you for your contribution!
Best regards,
Cornelis
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