same qvd with latest qvd load

[Manni_SM]

Hi All,

@Vegar  @rubenmarin 

i have qvd with below fields

load *

id

name

product

from   [lib://folder/currentqvd.qvd];

so my  count of id is =465 

this qvd will load everyday and that id count will update usually it will increase   

my requirement is  : if my id count is less than 465 it should load 465 load count qvd only  

how to do this in script?

[robert_mika]

maybe like 

First 465 load...

or First 465 load distinct

[Manni_SM]

All are distinct ids only 

Total count is 465 as of today (means yesterday qvd id count)

Example today qvd is loaded with 400 IDs then it should pic yesterday qvd 

Example today qvd is loaded with 500 ids count then it should load today qvd

[rubenmarin]

Hello, you can fist load the qvd just to do the count, if it has, store in a qvd with the data in a new qvd, like fullitemsqvd

Then read from this fullitemsqvd, it the currentqvd had the 465+ items these data would be in fullitemsqvd.qvd, if not, the fullitemsqvd will have the data of the last qvd that meets the requirement.

I would check if you really need to work that way, maybe the currentqvd has less than 465 items but some of the ids is a new id and you need those ids to be loaded to create all the relations.

[marksouzacosta · Accepted solution]

Hi @Manni_SM,

I think I got you. You are comparing previous read vs current read. You compare both and keep the highest distinct count of ID field values. My question is, how do you count current number of ID values?

Your code will be something like this:

// Get previous records
[Previous]:
LOAD
  id,
  name,
  product
From
  [lib://folder/currentqvd.qvd]
;

// Get current records
NoConcatenate

[Current]:
SQL SELECT
  id,
  name,
  product
From
  Source
;

// I'm assuming the ID values are already distinct, meaning, the number of rows is equals to count distinct ID
If NoOfRows('Previous') > NoOfRows('Current') Then
  TRACE Keep Previous;
  DROP TABLE [Current];
  // Do nothing.
Else
  TRACE Keep Current;
  DROP TABLE [Previous];
  STORE [Current] INTO [lib://folder/currentqvd.qvd](qvd);
  DROP TABLE [Current];
End If

This should give you a good direction. If not, please let us know more details about how you compare yesterday vs today ID counts.

Regards,

Mark Costa