topic Grouping data in a column in QlikView

Grouping data in a column

Thu, 26 Nov 2009 21:37:03 GMT

Hi, I have a set of data that I want to use,there is one column called object accounts, there are two distinct groups that these object account belong to, at the moment the column looks like this:

0123

0124

0125

0126

0127

0128

0129

0130

However there are two main groups within this column, how can I split it in two choosing which of the object accounts belongs in which group so I end up with a table that looks like this:

Labour

0123

0124

0125

0126

Projects

0127

0128

0129

Other

0130

I think the answer would be set analysis but I don't understand how to use this, all help gratefully recieved!

Grouping data in a column

Fri, 27 Nov 2009 02:48:07 GMT

I think the web-seminar 'Technical Pathway - Financial Reporting with QlikView' might give you some ideas for this when you like to show titles like "Labour" , "Projects" and "Other" in a table.

You could create a table that has each data entry appointed to a group. Use that to get the right project for each entry.

Labour 0123

Labour 0124

Project 0127

Project 0128

Other 0130

Grouping data in a column

Fri, 27 Nov 2009 02:49:28 GMT

Is it not easier to add a reference during the load of data? Something like

load * inline [

ObjectAccount, Group

0123,Labour

0124, Labour

0125, Labour

0126,Labour

0127,Project

0128, Project

0129, Project

0130, Others];

Grouping data in a column

Fri, 27 Nov 2009 16:55:01 GMT

Hi, how do I use an inline statement, I have tried with the following load statement:

LOAD [Account ID],

[Cost Centre],

[Object Account],

[Fiscal Year],

Type,

[Budget YTD],

Period,

[Budget Amount],

[Budget Description]

FROM

Data\Budget_Live.qvd

(qvd);

LOAD * Inline [

, Group

6001,Labour

6006, Labour

6061, Labour

6062,Labour

6065,Labour

6502, Labour

6113, OHD

6114, OHD

6122, OHD

6122, OHD];

However this doesn't work, how else should it be written?

Cheers

Ben

Grouping data in a column

Fri, 27 Nov 2009 17:10:23 GMT

LOAD * Inline [
'Cost Centre', 'Group'
6001,Labour
6006, Labour
6061, Labour
6062,Labour
6065,Labour
6502, Labour
6113, OHD
6114, OHD
6122, OHD
6122, OHD
];

P.S. Sorry for suggestion your question as answer.

Grouping data in a column

Fri, 27 Nov 2009 17:59:31 GMT

Thats ok, thanks very much for the answer!