Error while concatenating the data

[#1]

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

[Peter_Cammaert]

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

[#3]

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

[#4]

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.

[Peter_Cammaert]

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

Thanks.

[Peter_Cammaert]

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

[#7]

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

[Peter_Cammaert]

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

[#9]

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.

[#10]

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;