Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have multiple monthly qvd's
2017_01.qvd
2017_02.qvd
until 2019_03.qvd
I am pulling all these qvd's into my application.
The situation I am having is- 2017_01.qvd thru 2018_12.qvd - I have same number of fields in all the qvd's. Example 10 fields which are same in all the qvd's
In 2019_01.qvd and 2019_02.qvd, I have 11 fields (1 new fields was added in 2019/01)
In 2019_03.qvd- I have 12 fields (another new field was added in 2019/03).
In the application, what is the best approach to handle this situation?
Thanks in advance.
With the assumption that those dates are suffix on each qvd (e.g. Example2017_01.qvd) one option would be to force concatenation. It will create an asymmetric table but will save you to list fields and will work if you have another qvd structure in the future.
FinalTable: //Create a table to append all qvd
LOAD *Inline [
Placeholder];
Concatenate (FinalTable) //force concatenation
LOAD * From Example*.qvd (qvd); //adjust this based on you qvd names
Drop field Placeholder;
Yes, Dates are suffix on each qvd.
The new fields added are dimensions.
Can you please elaborate a little more on the solution you have provided?
Thank you!
If I understood correctly. You are QVD are the same data but split by YearMonth, and the QVD structure has changed over time. What I am suggesting is to do a load with out hardcoding field names or suffix (date).
The result will be a single table like :
The approach I am using right now is:
I have renamed DATA_2017_01.qvd to DATA201701.qvd (did this for 2017 and 2018 qvd's )
I have renamed DATA_2019_01.qvd to DATA_2_201901.qvd; DATA_2019_02.qvd to DATA_2_201902.qvd
I have left DATA_2019_03 as it is
So now I have 3 different name set for qvd
In the application, I am doing the following
Table1:
Load
Field1,
Field2 as xyz,
....
Field10
from DATA20*.qvd;
Outer Join
Table2:
Load
Field1,
Field2 as xyz,
...
Field10,
Field11
from
DATA_2_*.qvd;
Outer Join
Table3:
Load
Field1,
Field2 as xyz,
...
Field10,
Field11,
Field12
from
DATA_2019_*.qvd;
Is this a good approach?
These are monthly qvd's and cannot be relaoded every month.
1 qvd is created each month.
I think it's best to use your previous naming convention DATA_YYYY_MM.qvd and try the suggested approach, that will spare you of any future maintenance of the script.
I am not sure on how to apply the approach suggested by you. Is it possible for you to provide an example please?
That would be really helpful for me.
Just try:
AllQVD: //Create a base table to append all qvd
LOAD *Inline [
Placeholder];
Concatenate (AllQVD) //force concatenation
LOAD * From Data_*.qvd (qvd); //using your previous naming convention
DROP Field Placeholder; //not required
I cannot do
LOAD * From Data_*.qvd
because I have some fields that I have aliased in the application datapull.
I have updated my script in my earlier reply post.
Field2 as xyz,
Please advise