topic Re: Error while concatenating the data in QlikView

Error while concatenating the data

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

Iam looking for logic where iam trying to append the data into QVD file.

I will have the new file every hour or day which will be replaced with existing one.

I have a file which has data as below:

Ex: for 10.3.2017 (Samplefile.xlsx)

Sno	Name	IC	Date	CardNumber
7	XXX	123	10-03-17	123456
8	YYY	456	10-03-17	976544

Next day Ex: 11.3.2017(Samplefile.xlsx) i received a file:

Sno	Name	IC	Date	CardNumber
1	XXX	123	11-03-17	123456
2	YYY	456	11-03-17	976544
3	ZZZ	676	11-03-17	678678

I need to concatenate /append the data into 1 QVD file.

Created a TempQvd and tried to merge into 1 QVD, but i recevie the error : Table not Found. Concatenate(Append Data) Load * from Transactions.qvd.

Logic is as below:

vTransqvdExists = QVDpath;

LOAD * FROM Samplefile.xlsx;

If $(vTransqvdExists) then

//if exists

Store Trans into Transactions.qvd;

Temptrans:

LOAD * from Transactions.qvd;

STORE Temptrans into tempTrans.qvd;

AppendData:

LOAD * FROM tempTrans.qvd;

Concatenate (AppendData)

LOAD * from Transactions.qvd;

STORE AppendData into Transactions.qvd;

ELSE

//First time this qvd is created

Store Trans into Transactions.qvd;

ENDIF

Can anyone suggest where the code is wrong?

regards

sudhakar

Re: Error while concatenating the data

Peter_Cammaert — Tue, 21 Mar 2017 08:45:35 GMT

You are probably facing a so-called AUTOCONCATENATE, performed by QlikView whenever you don't really want one. I guess that the LOAD for AppendData will actually concatenate all data to table Temptrans, just because the tables look similar.

Add a DROP Table Temptrans; statement before loading AppendData

Re: Error while concatenating the data

Tue, 21 Mar 2017 09:01:59 GMT

Hi Sudhakar,

Have you tried something like this :

---------

sample:

LOAD

FROM Transactions.QVD; // Old data is stored here

concatenate

LOAD

FROM Samplefile.xlsx; // you get your data from the new xlsx file

STORE sample into Transactions.QVD; // finally you store the concatenation in the QVD file

--------

You can improve this code. It's just a little suggestion.

Yours

Re: Error while concatenating the data

Tue, 21 Mar 2017 09:20:37 GMT

Hi Peter,

Thanks for the quick reply.

I added the drop statement before AppendData: . No Error. But only the latest file (new file) data is loaded.

previous data is not appended.

Re: Error while concatenating the data

Peter_Cammaert — Tue, 21 Mar 2017 09:47:37 GMT

Can you post your actual script here? The one you posted under "Logic is as below" doesn't appear complete to me.

Thanks.

Re: Error while concatenating the data

Peter_Cammaert — Tue, 21 Mar 2017 09:57:13 GMT

Anyway, a role model could be (no prior loads needed, SampleFile.xlsx must exist, Transactions.qvd may exist or not):

LET vPrefix = 'Trans:';

IF Not IsNull(FileTime('Transactions.qvd')) THEN

LET vPrefix = 'CONCATENATE (Trans)';

Trans:

LOAD * FROM Transactions.qvd (qvd); // Load historical data

END IF

$(vPrefix)

LOAD * FROM SampleFile.xlsx (ooxml, ...); // Adjust the options

STORE Trans INTO Transactions.qvd (qvd);

DROP Table Treansactions;

Adjust the script according to your needs and actual file names and paths.

Best,

Peter

Re: Error while concatenating the data

Tue, 21 Mar 2017 10:04:18 GMT

below is the script:

SET qvdTransRoot = 'G:\QPublish\Transactions.qvd';

FOR Each File in FileList ('$(vRoot)'&'CardTransactionDataFeb16_*.xlsx')

SET vFileName = '$(File)';

NEXT File

//Check if File exists

LET vFileExists = if(FileSize('$(vFileName)') > 0,1,0);

LET vTransqvdExists = if(FileSize('$(qvdTransRoot)') > 0,1,0);

[Trans]:

LOAD

[Card No],

Sno,

Door,

AccessTime,

[Card Holder],

NRIC,

Floor,

Date,

TimeHour,

Verification,

Transaction

FROM

$(vFileName)

(ooxml, embedded labels, table is [Transactions]);

Inner Join (Trans)

LOAD

[Card No],

ID,

Name,

Title,

Department,

Division

FROM

(ooxml, embedded labels, table is UserRefData);

If $(vTransqvdExists) then

//New Data

Store Trans into G:\QPublish\Transactions.qvd;

Temptrans:

LOAD * from G:\QPublish\Transactions.qvd;

STORE Temptrans into (qvd); //To store Old Data

DROP table Temptrans;

AppendData:

LOAD * FROM (qvd); //Old Data

Concatenate (AppendData)

LOAD * from G:\QPublish\Transactions.qvd; //New Data

Store AppendData into G:\QPublish\Transactions.qvd;

ELSE

Store Trans into G:\QPublish\Transactions.qvd;

ENDIF

Re: Error while concatenating the data

Peter_Cammaert — Tue, 21 Mar 2017 10:16:02 GMT

The file name scanning code at the beginning of your script, what is it used for? The code actually picks out the last file in a possible series of files with similar names, but the order will be unpredictable.

And - if you have four source files that fit the file name mask - you only use the last one. What should happen with the others?

Peter

Re: Error while concatenating the data

Tue, 21 Mar 2017 10:27:42 GMT

this code : looks for the xlsx file - CardTransactionDataFeb16_0001. Now that we are in development phase, probably later we might have 2 files coming in like CardTransactionDataFeb16_0002.

FOR Each File in FileList ('$(vRoot)'&'CardTransactionDataFeb16_*.xlsx')

SET vFileName = '$(File)';

NEXT File

but for now we are having only 1 file with that name and no issues with filenames.

Re: Error while concatenating the data

Tue, 21 Mar 2017 12:20:27 GMT

Hi ,

//New Data

Store Trans into G:\QPublish\Transactions.qvd;

Temptrans:

LOAD * from G:\QPublish\Transactions.qvd;

STORE Temptrans into (qvd); //To store Old Data

If we see the above code,

1. New data is loaded into Trans table

2. Trans table data is stored into Transactions.qvd.

3. Now the Transactions.qvd data is overrided with new data.

4. Again you are pulling the data from Transactions.qvd into tempTrans.qvd, which will be new data only.

The old data is already lost at step 2.The code should be modified as shown below. Try once

//To store Old Data

Temptrans:

LOAD * from G:\QPublish\Transactions.qvd;

STORE Temptrans into (qvd);

//New Data

Store Trans into G:\QPublish\Transactions.qvd;

Re: Error while concatenating the data

Wed, 22 Mar 2017 06:44:23 GMT

Hi,

I tried changing the code as you have specified. But observed the old data is being cleared off in old QVD. only the new data gets loaded.Any idea on this?

Re: Error while concatenating the data

Wed, 22 Mar 2017 07:54:45 GMT

Hi Sudhakar,

Can you try the below code

If $(vTransqvdExists) then

//New Data

Concatenate(Trans)

LOAD * from G:\QPublish\Transactions.qvd;

Store Trans into G:\QPublish\Transactions.qvd;

ELSE

Store Trans into G:\QPublish\Transactions.qvd;

ENDIF

Re: Error while concatenating the data

Wed, 22 Mar 2017 08:43:03 GMT

Thanks Sandhya,

This codes works. Thanks a lot.

Re: Error while concatenating the data

Wed, 22 Mar 2017 08:46:12 GMT

No problem