topic Combine fields into one table in QlikView

Combine fields into one table

Wed, 07 Oct 2009 20:17:04 GMT

Hi,

I'm really new to reporting and have no background knowledge to back me up. Saying that I'm trying to get some data out of a Lotus Notes database to report on. In this database the information is stored in a number of fields, depending on the number of items on a site. I need to combine these fields into one column (I think) so I can then group the data together. Could someone point me in the right direction please?

Many thanks

Matt

Combine fields into one table

Wed, 07 Oct 2009 21:00:23 GMT

From your description, it sounds like you may need to look into Cross Table (page 480 in the QlikView 9 Reference Guide).

It allows you to take records that are spread across many different columns and put them into multiple rows for analysis.

Combine fields into one table

stevedark — Wed, 07 Oct 2009 21:46:40 GMT

Hi Matt,

I am not sure I follow what the data structure looks like from your description. Perhaps you could post the relevant parts of the schema?

The CrossTab suggestion works for a very specific layout of data, typically where you have a number of columns of description, then values for a number of items on the same row - say YTD figures, eg:

site,product,jan,feb,mar,apr,may,jun,jul,aug,sep,oct,nov,dec
UK,widgets,120,230,348,543,0,0,0,0,0,0,0,0
[...]

However, if your data is not structured like that it will not be the solution.

In short if you want to combine data from two different places into one column in QlikView ensure it has the same name. This can be done with the as statement. For example, if you have two tables with an name and a value, and you want to combine them it will look a bit like this:

Items:
Load
ID,
FirstField as Value;
FROM TableOne.[...]

Items:
Load
ID,
SecondField as Value;
FROM TableTwo.[...]

This will then come in as a two elements in QlikView, ID and Value - and the relationships shall be maintained. If there are ID's or Values that exist in both tables they will be identified as being the same and will only appear in the QlikView columns once. Note the prefix of Items: to force the two tables to appear as one in the QlikView table viewer.

I hope that makes sense - please post more details if you need further assistance.

Regards,
Steve

Combine fields into one table

Wed, 07 Oct 2009 22:32:35 GMT

Hi Steve,

I think I'm trying to run before I can walk. I should get to grips with Qlikview and some basic data before I go trying to report on quirky Lotus Notes databases.

Thank you all for your help

Matt