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

[big_dreams]

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,

[happyjoshua777]

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

[happyjoshua777]

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.

ta

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

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;

[happyjoshua777]

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

[big_dreams · Accepted solution]

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,

[happyjoshua777]

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

[big_dreams]

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,

[happyjoshua777]

QV and MyLoad tables are empty

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

[big_dreams]

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,

[happyjoshua777]

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