Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Best approach for datapull from multiple qvd's with condition

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.

 

9 Replies
Highlighted
Partner
Partner

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;

Highlighted
Creator II
Creator II

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!

Highlighted
Partner
Partner

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 :

seta.png

Highlighted
Creator II
Creator II

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?

Highlighted
Creator II
Creator II

These are monthly qvd's and cannot be relaoded every month.

1 qvd is created each month.

Highlighted
Partner
Partner

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.  

Highlighted
Creator II
Creator II

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.

Highlighted
Partner
Partner

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

Highlighted
Creator II
Creator II

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