Keep track of historical data

[Anonymous]

Hi

I want to keep track of historical data which mean i would like to show how many days does particular is open.

lets have a look at sample data

TestData1:

ID	Desc
1	AAA
2	BBB
3	CCC
4	DDD

TestData2:

ID	Desc
1	AAA
2	BBB
3	CCC
6	FFF

Now I need to check TestData2 with TestData and if any match is found between two tables it should return 1 else 0.

Likewise I need to append the Testdata2 to TestData1.

After appending TestData1 consists of TestData2 as well.

As it is daily reload it has check for match every day.Let's  say ID 1 is already existing TestData1 and TestData2 so it will return 1 .Now for the next time reload same ID 1 is present in the file it should get incremented by 1 .

i have tried below script somehow i tried matching records but i struck with incrementing the matched records.

TestData1:

laod ID,Desc

from TestData1.xls;

load *,

if(exists(ID),'1','0') as Idmatch

from TestData1.xls

Any Ideas !

Thanks

[marcohadiyanto]

Hi,

I think your case looks like incremental load.

you can find documents about incremental load and script in this link.

Incremental LoadIncremental Load

Incremental load for beginner with example

Regards,

Marco

[Gysbert_Wassenaar]

Perhaps like this:

TestData1:

LOAD

     ID,

     ID as Idmatch1,

     Desc,

     1 as Counter

FROM

     TestData1.xls (biff, embedded labels, table is $Sheet1);

IF FileTime('HistoricalData.qvd')>0 THEN

     HistoricalData:

     LOAD

          ID,

          ID as Idmatch2,

          Desc,

          RangeSum(If(Exists(Idmatch1, ID),1,0),Counter) as Counter

     FROM

          HistoricalData.qvd (qvd)

          ;

     DROP Field Idmatch1;

     CONCATENATE (HistoricalData)

     LOAD

          *

     RESIDENT

          TestData1

     WHERE

          Not Exists(Idmatch2, ID)

          ;

     DROP Field Idmatch2;

     STORE HistoricalData iNTO HistoricalData.qvd;

     DROP TABLE TestData1;

ELSE

     DROP Field Idmatch1;

     STORE TestData1 INTO HistoricalData.qvd;

ENDIF

[Anonymous]

I am getting error Counter not found in HistoricalData table .

[Gysbert_Wassenaar]

Sorry, no idea. Works fine here.

[Anonymous]

TestData1:

LOAD

     ID,

     ID as Idmatch1,

     Desc,

     1 as Counter

FROM

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

IF FileTime('C:\Users\Desktop\HistoricalData.qvd')>0 THEN

     HistoricalData:

     LOAD

          ID,

          ID as Idmatch2,

          Desc,

          RangeSum(If(Exists(Idmatch1, ID),1,0),Counter) as Counter

     FROM

          C:\Users\Desktop\HistoricalData.qvd (qvd)

          ;

     DROP Field Idmatch1;

     CONCATENATE (HistoricalData)

     LOAD

          *

     RESIDENT

          TestData1

     WHERE

          Not Exists(Idmatch2, ID)

          ;

     DROP Field Idmatch2;

     STORE HistoricalData INTO C:\Users\Desktop\HistoricalData.qvd;

     DROP TABLE TestData1;

ELSE

     DROP Field Idmatch1;

     STORE TestData1 INTO C:\Users\Desktop\HistoricalData.qvd;

ENDIF

I am Using above script but its giving error

Counter field not Found in Historical data

Thanks

[Gysbert_Wassenaar]

Post the qvw document, the source file Sample.txt and the qvd HistoricalData.qvd.

[Anonymous]

Hi

Sample Data is

ID,Desc

1,Apple

2,Orange

3,Pineapple

1,Apple

Thanks