topic Re: Table not found error when creating a table in QlikView

Table not found error when creating a table

happyjoshua777 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Table not found error when creating a table

Gysbert_Wassenaar — Sun, 01 Jul 2018 15:17:37 GMT

Resident Temp_QS

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

Re: Table not found error when creating a table

vishsaggi — Sun, 01 Jul 2018 15:22:00 GMT

‌cchange to Resident QV

Re: Table not found error when creating a table

Gysbert_Wassenaar — Sun, 01 Jul 2018 15:25:51 GMT

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.

Re: Table not found error when creating a table

happyjoshua777 — Mon, 02 Jul 2018 05:59:57 GMT

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

Re: Table not found error when creating a table

jonathandienst — Mon, 02 Jul 2018 06:14:31 GMT

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.

Re: Table not found error when creating a table

happyjoshua777 — Mon, 02 Jul 2018 06:16:30 GMT

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

Re: Table not found error when creating a table

kakani87 — Mon, 02 Jul 2018 06:19:14 GMT

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

Re: Table not found error when creating a table

happyjoshua777 — Mon, 02 Jul 2018 06:22:34 GMT

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)

Re: Table not found error when creating a table

jonathandienst — Mon, 02 Jul 2018 06:25:43 GMT

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;

Re: Table not found error when creating a table

big_dreams — Mon, 02 Jul 2018 06:39:12 GMT

Hi,

Debug your script step by step.

1: Load initial part only i.e.

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

Then check table viewer how many table are you getting , share screen shot of it.

2: Rename all fields like below

===========================================

QV:

LOAD

HireDate as qv_HireDate,

EndDate as qv_EndDate,

SalesEmployeeID as qv_SalesEmployeeID,

OrderDate as qv_OrderDate

FROM [$(vFilePath)IntervalMatch_QV.qvd] (qvd);

QS:

NoConcatenate

LOAD

HireDate as qs_HireDate,

EndDate as qs_EndDate,

SalesEmployeeID as qs_SalesEmployeeID,

OrderDate as qs_OrderDate

FROM [$(vFilePath)IntervalMatch_QS.qvd] (qvd);

[MyLoad]:

LOAD

Resident QS

Where NOT Exists(qv_HireDate,qs_HireDate);

STORE MyLoad INTO [..\MyLoad.qvd];

================================================

Share screen shot of table viewer or error if you get any.

Regards,

Re: Table not found error when creating a table

happyjoshua777 — Mon, 02 Jul 2018 06:39:53 GMT

the script below ends with now errors yet MyLoad contains no rows

===================================

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

NoConcatenate

LOAD

Resident QV

Where NOT Exists (QS_HireDate, HireDate);

STORE MyLoad INTO [..\MyLoad.qvd];

Re: Table not found error when creating a table

happyjoshua777 — Mon, 02 Jul 2018 06:45:17 GMT

thanks, this is your script with 2 minor corrections.

I still do not understand why my script (above) ended up not generating ANY rows for MyLoad

I successfully generated MyExceptions.qvd which contains 1-68 rows present in QV and absent in QS;

and 69-112 rows present in QS only.

I think can see why this has occured.

==========================

All:

LOAD

HireDate,

EndDate,

SalesEmployeeID,

OrderDate,

1 as SourceQV

FROM [$(vFilePath)IntervalMatch_QV.qvd] (qvd);

Join (All)

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;

Re: Table not found error when creating a table

happyjoshua777 — Mon, 02 Jul 2018 08:29:08 GMT

Adjusted my script as per you feedback above. Just added NoConcatenate to MyLoad table.

The result is the same, namely: QS_HireDate is not withing the scope of yLoad:

I have included the whole script bellow

QV and QS tables are successfully generated:

QV << IntervalMatch_QV (row-based qvd optimized) 791 Lines fetched

QS << IntervalMatch_QS (row-based qvd optimized) 768 Lines fetched

Error:

Field not found - <QS_HireDate>

[MyLoad]:

NoConcatenate

LOAD

Resident QV

Where NOT Exists (QV_HireDate,QS_HireDate)

================================================================

//Script

QV:

LOAD

HireDate as QV_HireDate,

EndDate as QV_EndDate,

SalesEmployeeID as QV_SalesEmployeeID,

OrderDate as QV_OrderDate

FROM [$(vFilePath)IntervalMatch_QV.qvd] (qvd);

QS:

NoConcatenate

LOAD

HireDate as QS_HireDate,

EndDate as QS_EndDate,

SalesEmployeeID as QS_SalesEmployeeID,

OrderDate as QS_OrderDate

FROM [$(vFilePath)IntervalMatch_QS.qvd] (qvd);

[MyLoad]:

NoConcatenate

LOAD

Resident QV

Where NOT Exists (QV_HireDate,QS_HireDate);

STORE MyLoad INTO [..\MyLoad.qvd];

//=======================================

thanks

Re: Table not found error when creating a table

big_dreams — Mon, 02 Jul 2018 09:51:49 GMT

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,

Re: Table not found error when creating a table

happyjoshua777 — Mon, 02 Jul 2018 10:25:53 GMT

Changed to:

...

[MyLoad]:

NoConcatenate

LOAD

Resident QV

Where NOT Exists (QS_HireDate,QV_HireDate);

Still MyLoad is not getting fetched with any data

Output:

QV << IntervalMatch_QV (row-based qvd optimized) 791 Lines fetched

QS << IntervalMatch_QS (row-based qvd optimized) 791 Lines fetched

MyLoad << QV 0 Lines fetched

QV End Date 73050 is $QV_end_date

$Syn 1 = QV_HireDate+QV_EndDate+QV_SalesEmployeeID+QV_OrderDate

thanks

Re: Table not found error when creating a table

big_dreams — Mon, 02 Jul 2018 10:40:03 GMT

Is it running without any error??

If yes share screen shot of table Viewer.

Now take list box of QS_HireDate and QV_HireDate in front end.

check both field have same data or different?

share screen shot of it.

Regards,

Re: Table not found error when creating a table

happyjoshua777 — Mon, 02 Jul 2018 10:50:55 GMT

QV and MyLoad tables are empty

List Boxes are getting populated by dimensions in $Syn 1... and QS tables

Re: Table not found error when creating a table

big_dreams — Mon, 02 Jul 2018 11:55:55 GMT

Bro as per your output of above comment both table contain 791 records .

Not understood where you are missing.

last tried.

Load only QV and QS table and check both hire date field record.

Is it same or different?

If I am not wrong both table have same hire date field record.

Regards,

Re: Table not found error when creating a table

happyjoshua777 — Mon, 02 Jul 2018 12:31:05 GMT

my apologies, I fixed the QS script which generated an identical qvd to QV output. These two QVDs were used to generate the last data model (above).

To really test script I'd have to introduce exactly the same bug again in order for the two qvds to have mismatched info.

I am not clear about

a) why QV table was not populated and instead I ended up with $Syn... table with data which was meant to go into QV table;

and b) Why QS table, which was populated with the same info did not get attahced to the synthetic link table.

thanks