Table not found error when creating a table

[happyjoshua777]

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

[Gysbert_Wassenaar]

Resident Temp_QS

I don't see any statement in your script that creates a table named Temp_QS

[vishsaggi]

‌cchange to Resident QV

[Gysbert_Wassenaar]

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.

[happyjoshua777]

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

[jonathandienst]

A couple of problems in your script:

• MyLoad will auto-concatenate into QS.
• You do not create a table Temp.
• The exists expects a value HireDate. This field is QS_Hiredate in QS.

That being said - this may not be the best way to get the result you describe.

[happyjoshua777]

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

[kakani87]

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

[happyjoshua777]

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

[jonathandienst]

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;