Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Team,
suppose there is a one value, is belongs to last month and stored in qvd. When we are taking qvd for update the records with where not exist(ID) condition then how particular ID's value will be changed?
LastDate:
LOAD
MAx("Date") as MaxDate
FROM [lib://QVDLOAD/Check.qvd]
(qvd);
let VmaxDate= peek('MaxDate',0,'LastDate');
Check:
LOAD
*
FROM [lib://DATASOURCE/check.xlsx]
(ooxml, embedded labels, table is Sheet1)
WHERE "Date">$(VmaxDate);
Concatenate
LOAD
*
FROM [lib://QVDLOAD/Check.qvd](qvd)
where not Exists(ID);
//We can use for delete.
Inner Join
LOAD
ID
FROM [lib://DATASOURCE/check.xlsx]
(ooxml, embedded labels, table is Sheet1);
Store Check into [$(QVDLOAD)Check.qvd](qvd);
Thanks in advance.
oh I forgot, WHERE "Date"> $ (maxDate) I would exchange it for
WHERE "Date"> = $ (VmaxDate);
because you could get records that were entered that same later than your recharge that same day, and you will not take it into account, in this way although you will have some records that you already have saved in your QVD, with the existing () you will keep the unique records.
Regards!!!
Okay, that way you also correct the modifications, which would take the part of the INNER from the end, since the incremental is no longer meaningful but you are simulating a full load, you bring all the IDs from the source table.
regards!!!
Thanks for your reply. Correct. If i use INNER JOIN ,there is no meaning with incremental. That table is also from data base it will take so much time. Where i need to take all ID's?
I am thinking to take QVD and incremental data with Where exists(ID). I dont know how it will work?
You should only use the exist () when you concatenate the news with the data in your QVD.
using where exist () you will keep the qvd load optimized, this generates a reduction in loading times, it is an excellent practice to use it whenever possible and avoid the use of inner or complex wheres.
The exist () that do not break with the optimized load are those that use a single parameter, if you need to use the second parameter it is no longer optimized.
Regards!!!
oh I forgot, WHERE "Date"> $ (maxDate) I would exchange it for
WHERE "Date"> = $ (VmaxDate);
because you could get records that were entered that same later than your recharge that same day, and you will not take it into account, in this way although you will have some records that you already have saved in your QVD, with the existing () you will keep the unique records.
Regards!!!
Thanks for the suggestion.
So you are saying that
WHERE "Date"> = $ (VmaxDate) along with where exists(ID). Correct? If it is, The problem is the value is changing for that ID directly with out any date. Example the value is replaced with 100 to 200 in database on 1st august2019 and it was stored. But it will not change again because "Date"> = $ (VmaxDate). How can i manage?
LastDate:
LOAD
MAx("Date") as MaxDate
FROM [lib://QVDLOAD/Check.qvd]
(qvd);
let VmaxDate= peek('MaxDate',0,'LastDate');
Check_temp:
LOAD
*
FROM [lib://QVDLOAD/Check.qvd](qvd);
Concatenate
LOAD
*
FROM [lib://DATASOURCE/check.xlsx]
(ooxml, embedded labels, table is Sheet1)
WHERE Exists(ID) and Date>=$(VmaxDate);
Thanks a lot. I found it.