Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
My QVD holds 2 years of data. And total row count 9000001. I am looking for number of rows from a QVD starting with two date range i.e. Minimum date and maximum date. But I am getting data for one date. Please advise.
Let vMaxdate= date#('31/05/2014','DD/MM/YYYY');
Let vMindate=date#('31/05/2016','DD/MM/YYYY');
Test Table:
Load *
FROM
(qvd)
WHERE TRANSACTION_DATE >= '$(vMaxdate)' and TRANSACTION_DATE <= '$(vMindate)';
LET a = NoOfRows('Test Table');
Thanks in Advance!!
Rohit
Hi Rohit,
Make sure the TRANSACTION_DATE field is properly formatted to date type, remove the quotes around the variables in the where clause and use $(#vMindate) for the values to be interpreted as numeric.
Also, the WHERE clause logic is wrong as no date can be in the same time grater than the max date and less than min date. It should be:
WHERE TRANSACTION_DATE >= $(#vMindate) AND TRANSACTION_DATE <= $(#vMaxdate)
Let vMaxdate= date#('31/05/2014','DD/MM/YYYY');
Let vMindate=date#('31/05/2016','DD/MM/YYYY');
Test Table:
Load *
FROM
(qvd)
WHERE TRANSACTION_DATE >= $(#vMindate) and TRANSACTION_DATE <= $(#vMaxdate);
LET a = NoOfRows('Test Table');
Regards,
David
Hi,
Try this.
Let vMaxdate= num(Date(date#('31/05/2014','DD/MM/YYYY')));
Let vMindate=num(Date(date#('31/05/2016','DD/MM/YYYY')));
Test_Table:
Load *
FROM
(qvd)
WHERE TRANSACTION_DATE >= '$(vMindate)' and TRANSACTION_DATE <= '$(vMaxdate)';
LET a = NoOfRows('Test_Table');
Regards,
Kaushik Solanki
Hi Kaushik,
I am getting below result:
vMaxdate = 41790
vMindate = 42521
And the Test_Table having 0 rows fetched after query execution.
Test_Table << MasterDatacsv 0 Lines fetched
a = 0 <-- it hold value as 0 record
Please advise.
Thanks,
Rohit
Hi Rohit,
Make sure the TRANSACTION_DATE field is properly formatted to date type, remove the quotes around the variables in the where clause and use $(#vMindate) for the values to be interpreted as numeric.
Also, the WHERE clause logic is wrong as no date can be in the same time grater than the max date and less than min date. It should be:
WHERE TRANSACTION_DATE >= $(#vMindate) AND TRANSACTION_DATE <= $(#vMaxdate)
Let vMaxdate= date#('31/05/2014','DD/MM/YYYY');
Let vMindate=date#('31/05/2016','DD/MM/YYYY');
Test Table:
Load *
FROM
(qvd)
WHERE TRANSACTION_DATE >= $(#vMindate) and TRANSACTION_DATE <= $(#vMaxdate);
LET a = NoOfRows('Test Table');
Regards,
David
Hi,
I got it.
I changes the sign of the where condition, but forgot to swipe the variables.
Change the vMaxdate to vMindate and vice versa.
so your Min Date should be 41790 and Max Date should be 42521
Regards,
Kaushik Solanki
Thanks a lot David.
Your suggested changes work as expected.
Thanks,
Rohit