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: 
happyjoshua777
Creator
Creator

Table not found error when creating a table

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

1 Solution

Accepted Solutions
big_dreams
Creator III
Creator III

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,

View solution in original post

19 Replies
Gysbert_Wassenaar

Resident Temp_QS

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


talk is cheap, supply exceeds demand
vishsaggi
Champion III
Champion III

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


talk is cheap, supply exceeds demand
happyjoshua777
Creator
Creator
Author

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
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
happyjoshua777
Creator
Creator
Author

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
Specialist
Specialist

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
Creator
Creator
Author

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
Partner - Champion III
Partner - Champion III

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;

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein