

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
check today vs yesterday count and generate a flag in case of reduction in count.
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
- Tags:
- count_records

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
