Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hello,
It was discovered that QV table contains 23 extra rows in comparison to QS table. In reality the number of rows should have the same # of rows.
the goal of the below script is to create a table called MyLoad which contains only those rows found in QV and not in QS.
However, I keep getting the following error:
Table not found
[MyLoad]:
LOAD
*
...
Please let me know if you have any ideas on how to resolve this error message and generate a small qvd file with MyLoad table's contents.
Thank you!
QV:
LOAD
HireDate,
EndDate,
SalesEmployeeID,
OrderDate
FROM [$(vFilePath)IntervalMatch_QV.qvd] (qvd);
QS:
NoConcatenate
LOAD
HireDate as QS_HireDate,
EndDate,
SalesEmployeeID,
OrderDate
FROM [$(vFilePath)IntervalMatch_QS.qvd] (qvd);
[MyLoad]:
LOAD
*
Resident Temp_QS
Where NOT Exists(QS_HireDate, HireDate);
STORE Temp INTO [..\MyLoad.qvd];
Hi,
one doubt from which table you want to do Resident QS or QV???
If Resident from QS then
Exists(qv_HireDate,qs_HireDate)
if Resident from QV table then
Exists(qs_HireDate,qv_HireDate)
Regards,
Resident Temp_QS
I don't see any statement in your script that creates a table named Temp_QS
cchange to Resident QV
Make sure that the records that you expect in table QV aren't appended to another already existing table with the exact same fields. Use a Noconcatenate Load if necessary.
thanks for your reply.
My bad, below is the actual script under discussion.
i did use NoConcatenate
===========================================
QV:
LOAD
HireDate,
EndDate,
SalesEmployeeID,
OrderDate
FROM [$(vFilePath)IntervalMatch_QV.qvd] (qvd);
QS:
NoConcatenate
LOAD
HireDate as QS_HireDate,
EndDate,
SalesEmployeeID,
OrderDate
FROM [$(vFilePath)IntervalMatch_QS.qvd] (qvd);
[MyLoad]:
LOAD
*
Resident QS
Where NOT Exists(QS_HireDate, HireDate);
STORE Temp INTO [..\MyLoad.qvd];
================================================
Here are the errors:
Field not found - <HireDate>
[MyLoad]:
LOAD
*
Resident QS
Where NOT Exists(QS_HireDate, HireDate)
Table not found
STORE Temp INTO [..\MyLoad.qvd]
QlikView Output:
QV << IntervalMatch_QV (row-based qvd optimized) 791 Lines fetched
QS << IntervalMatch_QS (row-based qvd optimized) 768 Lines fetched
$Syn 1 = EndDate+SalesEmployeeID+OrderDate
Thanks
A couple of problems in your script:
That being said - this may not be the best way to get the result you describe.
The goal is to fetch those rows in QV which are not found in QS.
Thus, I have:
...
Where NOT Exists(HireDate, QS_HireDate);
HireDate is the QV table field vis-a-vis QS_HireDate which is an alias in QS
I changed to :
...
Resident QV
accordingly and I am still getting:
Field not found - <QS_HireDate>
[MyLoad]:
LOAD
*
Resident QV
Where NOT Exists(HireDate, QS_HireDate)
Table not found
STORE MyLoad INTO [..\MyLoad.qvd]
=====================================
I presume this is due to not having scope in MyLoad table generator to QS_HireDate field and am not sure how to correct that as Resident can only refer to a single table (QV in my script).
thanks
Try noconcatenate for table Myload instead QS
QS:
LOAD
HireDate as QS_HireDate,
EndDate,
SalesEmployeeID,
OrderDate
FROM [$(vFilePath)IntervalMatch_QS.qvd] (qvd);
[MyLoad]:
NoConcatenate
LOAD
*
Resident QS
Where NOT Exists(QS_HireDate, HireDate);
STORE Temp INTO [..\MyLoad.qvd];
Point 2: I corrected that and substituted MyLoad in the command to create MyLoad.qvd;
Point 1: MyLoad was not getting auto-concatenated as the same number of lines were getting generated in QV and QS tables, respectively, as the number in the original QVDs;
Point 3: as, I have written, my intention in where not exists was to extract into MyLoad from QV 23 extra rows (comparison to QS table)
ta
Something like this:
All:
LOAD
HireDate,
EndDate,
SalesEmployeeID,
OrderDate,
1 as SourceQV
FROM [$(vFilePath)IntervalMatch_QV.qvd] (qvd);
Join (All) // outer join to keep all from both sources
LOAD
HireDate,
EndDate,
SalesEmployeeID,
OrderDate,
1 as SourceQS
FROM [$(vFilePath)IntervalMatch_QS.qvd] (qvd);
Exceptions:
NoConcatenate
LOAD *,
'In QV; Absent From QS' as Exception
Resident All
Where SourceQV = 1 and IsNull(SourceQS);
Concatenate(Exceptions)
LOAD *,
'In QS; Absent From QV' as Exception
Resident All
Where SourceQS = 1 and IsNull(SourceQV);
STORE Exceptions into Exceptions.qvd (qvd);
DROP Table All;