How can I use dates from different tables together?

[#1]

I  try to make one report with sales figures from five different sources.

The data comes in five different select statements and load into five different tables with a costumer key.

All sales have a date, like Sales1_date, sales2_date and so forth. When the data is loaded I have a joint between the five on the costumer key. But in order to use the dates in a pivot table I also named one column the same in all tables to be able to use one common date to summarize all the sales into one expression.

The problem is that in some way I cannot figure out this joining makes the sum of sales wrong.

Is there a better way to summarize five different sales figures for one costumer key and distribute it over a common dateline?

[kaushiknsolanki]

Hi,

     If your all source has the same numbers of fields with same data, then use the "concatenate" to concatenate the table instead of join.

     If not then you can use the concept of Linktable to have the date from all 5 tables and keep into one table which is link table and then use the Date from Link table.

Regards,

Kaushik Solanki

[#3]

Thanks,

I can restructure the tabels into:

Custumer_key - Date - Amount

I will then have five of these. when i Import them i have to make sure the kollums have differet headings so QV does not join them on every field in the import?

[Colin-Albert · Accepted solution]

You need to change your load script so you end up with a single sales table having columns for source, customer key, date and amount.

First load the data from source 1 and set the source field to 1 or the unique source name.

Then concatenated the data from source 2 into the table containing the source 1 sales data.

The source field will be 2 for this data.

Do this for your other sources and you will have a single table.

You can select one or more sources in your charts by using a listbox on the source field.

[jldengra]

The best way you ask for depends on your five sources dimensions and on the selectors needed for your QlikView application, as well as it depends on whether you need to show their data together only in a summary table or also separately in other tables or charts.

If your five sales sources have the same data structure and dimensions, then the best approach will be to integrate all of them into the same table in the QlikView model by loading them together, for example, by loading the first table with name and after that the other four without name but with the same dimension names so QlikView loads a single table, It is equivalent to concatenate.

On the other hand, if your sources have some different dimensions that are able to be selected in your application, and you need to also be able to isolate their information in separated tables or graphs, a single table is not proper since each time a field exclusive for one of the sources is selected, the sales of the sources without it will be excluded from the current selections, and this would be an unexpected behavior if you are intended to summarize sales for a given customer keys selection, In that case, you will also need separated tables in you QlikView model, and there will be different possible solutions (by using a linked table, by replicating their data integrated in a summary table in the model but with different column names, etc.).

[#6]

I restructured the select statements and added a source in eache. I then uploaded everything into one tabel. It works nice and clean!

Thanks all.