Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

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

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 :

Inc.PNG

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

Inc1.PNG

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

IncOut.PNG

Thanks In Advance

Munna

Thanks,
Srinivas
5 Replies
Highlighted
Master III
Master III

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

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.

Highlighted

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

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;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Not applicable

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

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!

Highlighted
Creator
Creator

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

Hi Digvijay,

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



Regards,

Srinivas

Thanks,
Srinivas
Highlighted
Creator
Creator

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

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

Thanks,
Srinivas