Number of new records since last update

[neha121600]

Hi,

I am trying get the number of new records which has been updated in latest refresh\reload.

Suppose we have a Table A which has column :

ID     Customer    Product

1             A                   XA

2             B                   YB

3             C                    ZC

and New records are

4             D                   XB

5              E                   XC

I just need the new record count(ID -4&5) which is 2. 

How can I achieve this?

Thanks

[tm_burgers]

Do you use an incremental load? or just reload the whole dataset each load?

If incremental you could use two variable in the load script; one let vRowsBefore = NoOfRows('DATA');

and one after the incremental load let vRows = NoOfRows('RESULT'); = NoOfRows('DATA')

then you could create a new variable - let vRowsNew = $(vRowsAfter) - $(vRowsBefore)

[neha121600]

Hi @tm_burgers , appreciate your quick response.

 We are doing the full load, is there any way we can do something like this in full load?

Please suggest.

[tm_burgers]

Yes, ok. It is a bit more complicated but see below. Essentially you will create a separate table that record all reload counts and timestamps them; and then you can use this table to pull the "First 2 Rows" then use that created table to calculate your difference.  Ie - Sum( {<RowNum={'1'}>}    CountRows)-Sum( {<RowNum={'2'}>}    CountRows)

[DataLoad]:
LOAD
    DataID,
    Value
FROM [lib://Qlik Data/DatLoadTest1.xlsx]
(ooxml, embedded labels, table is Sheet1);

// Run this part of the Script initially without the next section to create your baseline QVD
// [CountLoad]:
// LOAD
// 	count(DataID) as CountRows,
//     Timestamp(now()) as TimeStamp
// Resident DataLoad;

// Store * from CountLoad into 'lib://CommonLookups)/CountLoad.qvd';


//This section will run each time you reload, it will open the existing dataset of Count(ID) and timestamps, and then concatenate a new row with the new count. 
[CountLoad]:
LOAD
    CountRows,
    "TimeStamp"
FROM [lib://CommonLookups (killam_tmccann)/CountLoad.qvd]
(qvd);

Concatenate (CountLoad)
LOAD
	count(DataID) as CountRows,
    Timestamp(Now()) as TimeStamp
Resident DataLoad;

Store * from CountLoad into 'lib://CommonLookups/CountLoad.qvd';

[FirstTwoRows]:
NoConcatenate First 2 LOAD
RowNo() as RowNum,
CountRows,
TimeStamp
resident CountLoad
where 1=1
order by TimeStamp desc;

drop table CountLoad;

[neha121600]

Hi,
Can you please explain what you did in First Two Rows table.
Thanks

[tm_burgers]

First two rows table: using  First 2 LOAD grabs the metrics for the last two loads, using order by timestamp desc it will pull just the latest load and the previous one. You must NoConcatenate so that it doesn't just match it with the CountLoad table and then concat it. I add the RowNo() so that you can use the set analysis equation above for your KPI.

You must make it a "non-optimized" load, which is why we add the Where 1=1

[FirstTwoRows]:
NoConcatenate First 2 LOAD
RowNo() as RowNum,
CountRows,
TimeStamp
resident CountLoad
where 1=1
order by TimeStamp desc;

let me know if you have any other questions specifically.