topic Re: Loading data in wrong table in QlikView

Loading data in wrong table

Mon, 18 Jun 2012 08:48:06 GMT

Does anyone know why the following is happening?

I think I am loading data into NoCommunicationTmp but it is actually inserting all of that data into another table (SmokeDetectionTmp).

I am out of ideas why this is occurring, any help is welcome.

Re: Loading data in wrong table

hps — Mon, 18 Jun 2012 08:50:36 GMT

Hi,

You've only got one dot (.) in the FROM PATH.

So the file cannot be loaded. Try changing it to "..\" instead.

Regards,

Hampus

Re: Loading data in wrong table

Mon, 18 Jun 2012 08:54:34 GMT

What do you mean, because I can't follow. Where should the dot be ?

Here is an extract of the screenshot:

BlowOutsTmp:

LOAD

timestamp#(DATE_TIME) as DateTime,

LINENUMBER as LineNumber,

ORIGIN as Origin

FROM

[$(CSV_PATH)\*.nsp.blowOut.csv]

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

Execute cmd /c del /Q $(CSV_PATH)\*.nsp.blowOut.csv;

/******************************************************************************/

/* Load the times there is no communication with the lines. */

/******************************************************************************/

NoCommunicationTmp:

LOAD * FROM [$(CSV_PATH)\NoCommunication.qvd](qvd);

NoCommunicationTmp:

LOAD

timestamp#(DATE_TIME) as DateTime,

LINENUMBER as LineNumber

FROM

[$(CSV_PATH)\*.nsp.noCommunication.csv]

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

Execute cmd /c del /Q $(CSV_PATH)\*.nsp.noCommunication.csv;

The CSV_PATH is defined as follows:

SET CSV_PATH = .\Data\Aerocom

Message was edited by: uzg_developer

Re: Loading data in wrong table

hps — Mon, 18 Jun 2012 09:13:58 GMT

Hi again,

My bad. Saw the ".\" part in the NoCommunicationTmp From bit as an incorrect form of "..\" which moves up one folder. Nothing incorrect there.

Have you checked that the NoCommunication.qvd file exists in the the path .\Data\Aerocom\NoCommunication.qvd? Could be the error.

Regards,

Hampus

Re: Loading data in wrong table

Mon, 18 Jun 2012 09:26:29 GMT

The main problem is that it loads the NoCommunicationTmp data into the SmokeDetectionTmp...

Here is the definition of SmokeDetectionTmp. Yes I know that they have the same structure, but the data is completly different so I can't merge these.

SmokeDetectionTmp:

Load * FROM [$(CSV_PATH)\SmokeDetection.qvd](qvd);

SmokeDetectionTmp:

Load

timestamp#(DATE_TIME) as DateTime,

LINENUMBER as LineNumber

From

[$(CSV_PATH)\*.nsp.smokeDetection.csv]

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

Execute cmd /c del /Q $(CSV_PATH)\*.nsp.smokeDetection.csv;

Re: Loading data in wrong table

Anonymous — Mon, 18 Jun 2012 09:35:49 GMT

Here's your problem:

Since the tables have the exact same structure they will be automatically concatenated.

Look up automatic concatenation in the manuals.

You'll need to either use NoConcatenate or alter the field names so you don't have an identical field structure between the tables.

Re: Loading data in wrong table

hps — Mon, 18 Jun 2012 09:39:08 GMT

Hi,

If that's the problem then you should add the "noconcatenate" before the load statement of the NoCommunicationTmp sections, but you'll get synthetic keys (more keys linking the data).

So to avoid that you should instead encapsulate the NoCommunicationTmp with a qualify *;

The qualify will cause the field names from the table NoComm.. to be prefixed with the tablename.

In other words will LineNumber from NoComm.. be called NoCommunicationTmp.LineNumber, and it will no longer be linked or concatenated with the SmokeDetectionTmp.

Remember to add the unqualify *; when you're done with the NoCommunicationTmp.

Regards,

Re: Loading data in wrong table

Mon, 18 Jun 2012 09:48:35 GMT

The NoConcatenate causes my load with * (star) to create hundreds/thousands of different tables, that kind of sucks 🙂

Re: Loading data in wrong table

hps — Mon, 18 Jun 2012 09:55:59 GMT

That's because you define NoCommunicationTmp more than once.

If you comment out the first NoCommunicationTmp load statement the NoCommunicationTmp tablename won't be increment with each file loaded..

Or you could add the following prior to the first NoCommunicationTmp load:

NoCommunicationTmp:

noconcatenate LOAD * INLINE [

LineNumber

];

But you'll still get the synthetic keys so you should rename the fieldnames a bit

With the code above you have to remove the "noconcatenate" part from the two NoCommunicationTmp tables following