Loading tables in a specific order

[#1]

If I want to load three tables A, B, and C and each time apply the load to the previous table ie so:

MyTable_A:

Load *

from ---- raw_data_file.xlsx

MyTable_B:

Load*

from My_Table_A

MyTable_C:

Load *

from

My_Table_B

STORE into MY_EXCEL_SHEET.xlsx

Do I need to store each table into a QVD ? What would be the simplest way to store each table then recall each ?

Thanks

[sunny_talwar]

I am not entirely sure what the goal is? Do you want to create three QVDs and then add those three qvds back into your application?

[#3]

I am applying rules / mappings in three stages, each time applying the next rule to the resultant load. Does that make sense ?

[Kushal_Chawda]

What you are trying to do here. If you want to recall the same table then use Resident or Preceding load

I would suggest you to go for preceding load for transformation like below

// Using Preceding load

TableA:

LOAD *

WHERE ... ;

LOAD *

WHERE ...   ;

LOAD *

FROM Raw_data.xls;

Store TableA into TableA.qvd;

or

// Using resident load

TableA:

LOAD *

FROM Raw_data.xls;

TableB:

noconcatenate

LOAD *

Resident TableA

Where <condition>;

drop table TableA;

TableC;

noconcatenate

LOAD *

Resident TableB

Where <condition>;

drop table TableB;

Store TableA into TableA.qvd;

I don't know why you want to store the all transformation stages into the QVD. Your final transformation stage will be enough for your requirement

[sunny_talwar]

May be this:

MyTable_A:

Load *

from ---- raw_data_file.xlsx

STORE MyTable_A into A.qvd(qvd);

DROP Table MyTable_A;

MyTable_B:

Load *

from A.qvd(qvd);

STORE MyTable_B into B.qvd(qvd);

DROP Table MyTable_B;

MyTable_C:

Load *

From B.qvd(qvd);

STORE MyTable_C into C.qvd(qvd);

Although, if you are performing transformations at each level, I don't see a point of doing this because the qvd loads won't be optimized and I don't see any performance benefits compared to resident or preceding load here

[Colin-Albert]

One other point, you line  "STORE into MY_EXCEL_SHEET.xlsx" will not work.

The Store command will create the file as a QVD or a CSV. Creating a XLSX file is not an option.

[#7]

Have gone down the QVD route since im dealing with very large datasets.

I tried this but the store statement (below) returns an 'execution of script failed' error ...

STORE MyTable_A into A.qvd(qvd);

DROP Table MyTable_A;

[trdandamudi]

The script is correct and I am not sure why you are getting the error. Can you post the script with sample data.

[sunny_talwar · Accepted solution]

May be check the path of where you are saving. Make sure you have read/write access to the location. Also check if there is an existing qvd which is in use for some reason. Also, if possible, share a screenshot of the error you are getting

[jonathandienst]

That error on the store could mean one of:

• The file is locked by another instance (usually of QV, perhaps a failed operation that has not released the lock)
• The file path does not exist.
• The user account does not have write access to the file path or to the file.
• Out of space on the physical drive.

There may be others, but these are what I would investigate first.