We need to show only updated and new records in separate table

[Srinivas]

Hi All,

I need one incremental concept related requirement but not same, this is tricky to achieve the output, could you please help me as soon as. 

See my code:

Incr:
LOAD ID,
Name,
Location,
Sales,
[Posting Date]

FROM
[..\Incr.qvd]
(qvd);
  LOAD
  Max(ID) as MaxId
  Resident A;
  Let MaxId = Peek('MaxId',-1);
DROP Table A;
NewProd:
LOAD ID,
Name,
Location,
Sales,
[Posting Date

FROM
[..\Incr.xlsx]
(ooxml, embedded labels, table is Sheet1) Where ID > $(MaxId);
Concatenate
LOAD ID,
Name,
Location,
Sales,
[Posting DateFROM
[..\Incr.qvd]
(qvd) Where Not Exists(ID);
  Inner Join
LOAD ID,
Name,
Location,
Sales,
[Posting Date]

  [..\Incr.xlsx]
( ooxml, embedded labels, table is Sheet1);

STORE NewProd into Incr.qvd;
Above code working fine but it was not reached my output

Initial table :

New data will added next reload time(or Next Day)

OUTPUT:Below output we need to show only updated and new records in straight table

Thanks In Advance

Munna

[Digvijay_Singh]

Your output is exactly same as Table2. Do you have this new data coming from separate file, you could directly use that instead of merging it with existing data and then extracting new and updated one.

[Anil_Babu_Samineni]

In your code, how to get the data. You AD looking Incr but your resident looking A

Check that first

Incr:
LOAD ID,
Name,
Location,
Sales,
[Posting Date]

FROM
[..\Incr.qvd]
(qvd);
  LOAD
  Max(ID) as MaxId
  Resident A;
  Let MaxId = Peek('MaxId',-1);

//returns the value of MaxId from the first last record read into the current internal table. But, Here you are calculating Max(ID) only. How this identify to show
DROP Table A;
NewProd:
LOAD ID,
Name,
Location,
Sales,
[Posting Date

FROM
[..\Incr.xlsx]
(ooxml, embedded labels, table is Sheet1) Where ID > $(MaxId);
Concatenate
LOAD ID,
Name,
Location,
Sales,
[Posting DateFROM
[..\Incr.qvd]
(qvd) Where Not Exists(ID);
  Inner Join
LOAD ID,
Name,
Location,
Sales,
[Posting Date]

  [..\Incr.xlsx]
( ooxml, embedded labels, table is Sheet1);

STORE NewProd into Incr.qvd;

[#4]

is the possible to add rowno() or use current loading time to generate a number. Use it to order the list might show you from the latest record!

[Srinivas]

Hi Digvijay,

Second table I showed for understanding purpose only but those records are directly coming from database itself.

Regards,

Srinivas

[Srinivas]

Anil Babu,

Please find the code, this is actual code.

Incr:

LOAD ID,

     Name,

     Location,

     Sales,

     [Posting Date]

FROM

Incr.qvd

(qvd);

LOAD

     Max(ID) as MaxId

Resident Incr;

Let MaxId = Peek('MaxId',-1);

DROP Table Incr;

NewProd:

LOAD ID,

     Name,

     Location,

     Sales,

     [Posting Date]

FROM

Incr.xlsx

(ooxml, embedded labels, table is Sheet1) Where ID > $(MaxId);

Concatenate

LOAD ID,

     Name,

     Location,

     Sales,

     [Posting Date]

FROM

Incr.qvd

(qvd) Where Not Exists(ID);

Inner Join

LOAD ID,

     Name,

     Location,

     Sales,

     [Posting Date]

FROM

Incr.xlsx

(ooxml, embedded labels, table is Sheet1);

STORE NewProd into Incr.qvd;

Regards

Munna