check today vs yesterday count and generate a flag in case of reduction in count.

[rupaliqlik]

Hi Experts,

               I need your help.I want to create a flag where I have to find out whether my today's qvd record count is greater than yesterday's qvd count.If it is not then there must be some issue in incremental.So i want to create a flag Record_Difference Y and N.

  In below example Record count is greater than yesterday's count.  Can you hrlp me.?

%QVDName	%LoadEnd	%NumRows
Sales.qvd	25/09/2018 9:38:01 AM	118899614
Sales.qvd	24/09/2018 11:21:21 AM	118697781
Sales.qvd	22/09/2018 9:28:02 AM	118677691
Sales.qvd	21/09/2018 12:20:51 PM	118608351
Sales.qvd	20/09/2018 3:22:37 PM	118530018
Sales.qvd	19/09/2018 10:31:33 AM	118297348
Sales.qvd	18/09/2018 1:12:38 PM	118243042

cuvmarcus_sommerbwisealiahmad  stalwar1‌ kaushik.solanki‌

Thanks ,

R .E

[timpoismans]

Hi R.E.

I've used the sample data you provided and did the following:

Temp_Load:

LOAD * Inline

[

%QVDName,%LoadEnd,%NumRows

Sales.qvd,25/09/2018 9:38:01 AM,118899614

Sales.qvd,24/09/2018 11:21:21 AM,118697781

Sales.qvd,22/09/2018 9:28:02 AM,118677691

Sales.qvd,21/09/2018 12:20:51 PM,118608351

Sales.qvd,20/09/2018 3:22:37 PM,118530018

Sales.qvd,19/09/2018 10:31:33 AM,118297348

Sales.qvd,18/09/2018 1:12:38 PM,118243042

];

Temp_Load:

NoConcatenate

Load

*,

    If(%NumRows>Previous([%NumRows]),1,0) AS CheckIncrementLoad

Resident Temp_Load

Order By %LoadEnd;

Drop Table Temp_Load;

With the following result:

Shows 0 when the %NumRows is lower than the previous, 1 if it's higher.

[joseph_eftamand]

Try storing in variables?

Freehand :

TodayCount:

Load

Date,

Count(Rows) as RowCount

Resident FactTable

Where Date = Date(Today())

Group By Date;

LET vTodayCount =  peek(RowCount,0,TodayCount);

Concatenate(TodayCount)

Load

Date,

RowCount

From Historical.qvd(qvd);

YesterdayCount:

Load

Date,

Count(Rows) as RowCount

Resident TodayCount

Where Date = Date(Today() - 1)

Group By Date;

Drop Table TodayCount;

LET vYesterdayCount =  peek(RowCount,0,'YesterdayCount');

Drop Table YesterdayCount;

You can then compare the two variables and see the difference / create a flag.

You can do that in the front end or a load statement.

Hope this helps.

[rupaliqlik]

Hi Tim,

     Your logic is ok.But I want to fetch only latest two dates and compare between them.In above example 24 and 25 date.I want to compate data for today vs yesterday count.If bychance any day extacter gets failed or not triggerds from qmc then I could not get count for yesterday so at that time it should peek day before yesterday count and compare with today's record count.

Thanks in advance.

[timpoismans]

Hi R.E.

Fetching only the two first dates would be dangerous, exactly because of the reason you specified. If something went wrong and there wasn't a load yesterday, you can't compare your %NumRows of today to anything.

That said, I used the following dummy data:

%QVDName	%LoadEnd	%NumRows
Sales.qvd	25/09/2018 9:38	118899614
Sales.qvd	24/09/2018 11:21
Sales.qvd	22/09/2018 9:28	118899615
Sales.qvd	21/09/2018 12:20	118608351
Sales.qvd	20/09/2018 15:22	118530018
Sales.qvd	19/09/2018 10:31	118297348
Sales.qvd	18/09/2018 13:12	118243042

So we got no data for the 24th, as in, a load failed.

The following code checks the row before, so the 22nd:

Check:

NoConcatenate

Load

*,

    If(IsNull(Previous(%NumRows)),

    If(%NumRows>Previous(Previous([%NumRows])),1,0),

    If(%NumRows>Previous([%NumRows]),1,0)) AS CheckIncrementLoad

Resident Temp_Load

Order By %LoadEnd;

The issue with this is that it will only skip one row in case you have no %NumRows. If there were multiple bad rows, as in no values for %NumRows, it won't go further back than 1 row.