topic Re: Trying to concatenate two data sets in QlikView

Trying to concatenate two data sets

Anonymous — Tue, 11 Aug 2015 14:54:58 GMT

Hi all,

I have an issue with two tables of data which contain field values for a list of items (services in this case).

The first table contains the current live values of each field, while the second table contains the historical values prior to each update.

My goal is to use the data from both tables so I can identify when changes were made and the corresponding value changes.

Below are examples of how the tables appear currently:

Live Data

Service_ID	Service_Version_ID	Service_Colour	Date
461987	109082	Blue	07-Aug-2015
723145	607288	Yellow	03-Aug-2015
998422	723811	Green	04-Aug-2015

Historical Data

Service_ID	Service_VersionHist_ID	Service_HistColour	HistDate
461987	107334	Red	25-Jun-2015
461987	105969	White	14-May-2015
723145	605844	Blue	17-Jul-2015
723145	605118	Yellow	09-Jul-2015
998422	711865	White	04-Mar-2015
998422	709453	Blue	23-Feb-2015

What I am trying to get is a view where I can map the historical fields with the corresponding live fields (Version_ID, Service_Colour, Date).

My goal is to then be able to use a global calendar to filter for colour changes over periods of time. I.e. Identify the previous Service_Colour for each Service_ID and the date on which it was implemented.

I had tried to do this is a different way using mapping but did not get the required output.

Does anyone have an idea on the most effective way to do this?

Thanks very much.

Re: Trying to concatenate two data sets

petter — Tue, 11 Aug 2015 15:02:05 GMT

You can connect the two tables via the SERVICE_ID field but then you will have to use an aggregation function in the expressions that involve the Historical_Data table such as Max(HistDate) as an example. Or you create a concatenated field that will serve as the link between the two tables with is comprised of Service_ID & '-' & HistDate.

Re: Trying to concatenate two data sets

richard_chilvers — Tue, 11 Aug 2015 15:09:22 GMT

I have rarely used CONCATENATE.

Is it possible to keep the structure of 'Live_Data' by using:

CONCATENATE LOAD Service_ID, Service_VersionHist_ID AS Service_Version_ID etc.... FROM Historical_Data' ?

This would then give a date-sequenced history of all service colours for each Service_ID.

Re: Trying to concatenate two data sets

maxgro — Tue, 11 Aug 2015 15:09:48 GMT

and what about a single table with a flag to identify live and historical?

you can get the full history just ordering the date

Re: Trying to concatenate two data sets

Anonymous — Tue, 11 Aug 2015 15:12:09 GMT

Hi Petter,

Thanks for the quick reply.

I have connected them using the common field Service_ID, but I am struggling to map the remaining historical fields with the live ones, to create one overall field for each.

Re: Trying to concatenate two data sets

Gabriel — Tue, 11 Aug 2015 15:13:02 GMT

Hi,

1st thing I will do is to rename HistDate as Date so that when you concatenate you have 1 field,and have extra field for both tables, 0 AS Source_Table in Live table and 1 AS Source_Table in Historical table.

To create your Master Calendar just take the Min and Max of Date field and build your master calendar from this 2 fields.

You can then use Set Analysis to differentiate between Historical Data and Live Data in the chart.

Hope this helps

Re: Trying to concatenate two data sets

Anonymous — Tue, 11 Aug 2015 15:14:15 GMT

That would be ideal scenario. Would a simple join work in that case, or would some mapping be needed?

Re: Trying to concatenate two data sets

Anonymous — Tue, 11 Aug 2015 15:19:16 GMT

Thanks Gabriel.

Would you be able to provide a basic example of your suggestion? I can upload a qvw file if needed.

Re: Trying to concatenate two data sets

maxgro — Tue, 11 Aug 2015 15:27:13 GMT

I thought something like

singletable:

load

Service_ID,

Service_Version_ID,

Service_Colour,

Date,

'L' as LiveOrHist

from livedata;

concatenate (singletable)

load

Service_ID,

Service_VersionHist_ID as Service_Version_ID,

Service_HistColour as Service_Colour,

Date,

'H' as LiveOrHist

from histdata

Re: Trying to concatenate two data sets

Gabriel — Tue, 11 Aug 2015 15:38:50 GMT

That's the idea Massimo Grossi gave you.

Just follow that

Re: Trying to concatenate two data sets

Anonymous — Tue, 11 Aug 2015 16:30:31 GMT

Thanks both.

This worked nicely to add the history data as new rows in the master table.

I encounter an issue with one of my datasets however, where the Live Data table has some additional fields which are are not in the Historical Data table.

As a result they return null values for the historical entries, whereas I would like them to return the same value that is in the live table. These are fixed values which do not get updated, so showing the live vlaue would be all I need.

Is mapping the best solution to resolve this, before concatenation?

Re: Trying to concatenate two data sets

maxgro — Tue, 11 Aug 2015 18:24:35 GMT

It's a solution.

Another could be to add a table with these fixed values fields and link this new table with the fact table by Service_ID (or some other fields).

We (community) have a partial view of your model , requirements, etc, ...... We can try to give different solutions, it's hard to say which is the best.

Re: Trying to concatenate two data sets

Gabriel — Tue, 11 Aug 2015 20:26:02 GMT

Hi,

I suggest you post sample data

Re: Trying to concatenate two data sets

Anonymous — Wed, 12 Aug 2015 09:53:00 GMT

Hi Gabriel,

Attached is a basic version with mapping used to add any extra fields.

This is ok in the instance where I only map a couple of fields, but I'm aware the way it's done may not be the most efficient of mapping multiple fields.

Thanks for all your help. This has pretty much resolved the problem.