topic Re: Loading tables in a specific order in QlikView

Loading tables in a specific order

Fri, 06 May 2016 10:41:14 GMT

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

Re: Loading tables in a specific order

sunny_talwar — Fri, 06 May 2016 10:44:55 GMT

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?

Re: Loading tables in a specific order

Fri, 06 May 2016 10:46:23 GMT

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

Re: Loading tables in a specific order

Kushal_Chawda — Fri, 06 May 2016 10:49:49 GMT

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;

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

Re: Loading tables in a specific order

sunny_talwar — Fri, 06 May 2016 10:54:36 GMT

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

Re: Loading tables in a specific order

Colin-Albert — Fri, 06 May 2016 10:56:06 GMT

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.

Re: Loading tables in a specific order

Fri, 06 May 2016 13:48:52 GMT

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;

Re: Loading tables in a specific order

trdandamudi — Fri, 06 May 2016 13:58:53 GMT

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

Re: Loading tables in a specific order

sunny_talwar — Fri, 06 May 2016 14:05:21 GMT

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

Re: Loading tables in a specific order

jonathandienst — Fri, 06 May 2016 14:07:40 GMT

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.

Re: Loading tables in a specific order

Fri, 06 May 2016 14:10:06 GMT

Ok managed to get it working thanks !