Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
merrillbrink
Contributor II
Contributor II

Trying to concatenate two data sets

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_IDService_Version_IDService_ColourDate
461987109082Blue07-Aug-2015
723145607288Yellow03-Aug-2015
998422723811Green04-Aug-2015

Historical Data

Service_IDService_VersionHist_IDService_HistColourHistDate
461987107334Red25-Jun-2015
461987105969White14-May-2015
723145605844Blue17-Jul-2015
723145605118Yellow09-Jul-2015
998422711865White04-Mar-2015
998422709453Blue23-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.

1 Solution

Accepted Solutions
maxgro
MVP
MVP

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

View solution in original post

13 Replies
petter
Partner - Champion III
Partner - Champion III

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.

richard_chilvers
Specialist
Specialist

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.

maxgro
MVP
MVP

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

you can get the full history just ordering the date

merrillbrink
Contributor II
Contributor II
Author

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.

Gabriel
Partner - Specialist III
Partner - Specialist III

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

merrillbrink
Contributor II
Contributor II
Author

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

merrillbrink
Contributor II
Contributor II
Author

Thanks Gabriel.

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

maxgro
MVP
MVP

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

Gabriel
Partner - Specialist III
Partner - Specialist III

That's the idea Massimo Grossi gave you.

Just follow that