Best practice for creating QVD from table with many records

[pam1990]

We have tables where one table holds current real time data and a similar table that holds historical data.

The additional differences in the tables are the names of the tables, of course, the suffix of the field names, and the historical tables have an accounting date and the current real time tables do not.

The suffix difference is that for the current tables value fields end with _c, but in the historical tables those fields end with _p. 

Historical tables can have a great deal of data, naturally.

Our accounting area would like data from both the current real time data tables and from the historical table tables.

They would also like select the accounting date. The accounting date is always a month end date.

I am in the process of creating QVDs for their apps and was curious as to the best approach to handle their request?

Likewise, I am wondering what the app should include so they can do things like select a desired accounting date or accounting date range?

[marcus_sommer · Accepted solution]

I suggest to make both sources from the data-structure equally - means to have the same fields and fieldnames. To be able to differ in expressions or per selections between them you could add a field [Source]. Further if fields may have no values like your mentioned accounting date you should set a default value like 'no date' to be able to select those values directly (NULL couldn't be selected).

Because of your mentioned many records I recommend to consider an incremental load-approach.

- Marcus

[pam1990]

Thanks @marcus_sommer 

I am not following "make both sources from the data-structure equally - means to have the same fields and fieldnames."

Do you have an example of what you mean?

[JordyWegman · Accepted solution]

Hi Palm,

What @marcus_sommer means is that you need the same fieldnames. Example:

New table:
Key_c	Date_c	Amount_c
10231	13-10-2020	500

Historic table:
Key_p	Date_p	Amount_p
8465	26-02-2018	345

When you want to concatenate these (add them together), you will get the following. Because the names are not matching.
Key_c	Date_c	Amount_c	Key_p	Date_p	Amount_p
10231	13-10-2020	500	
	                        8465	26-02-2018	345

Better would be that you rename on of the fields and add an indicator (_indSource) that tells you from which source it comes.
Key_c	Date_c	Amount_c	_indSource
10231	13-10-2020	500	Current

Concatenate these two
Key_c	Date_c	      Amount_c	_indSource
8465	26-02-2018	345	Historic

Results in:
Key_c	Date_c	Amount_c	_indSource
10231	13-10-2020	500	Current
8465	26-02-2018	345	Historic

This way you can create formulas like Sum({$< _indSource = {'Current'}>}Amount_c) where you will only get the current values, or of course the other way around.

Sorry for the layout but this site has some problems with their own HTML messages.. That why the structure is like this an not in tables..

Jordy

Climber

[pam1990]

Thanks @JordyWegman 

Is the renaming of the fields in Qlik Sense an alias?

[pam1990]

I found this Rename field from particular table . I will give this a try.