How can I write this as Set Anaylsis?

[#1]

Hi everyone,

My question is like that: in the file ID is a key file. And each ID has 2 row of records. One row for gender, one row for class:

Gender: Male, Female and Not Disclosed

Class: 1, 2, 3, 4 ,5 and 6

The is a common term named: "Weight".

Therefore we have total Weight = 1 for all ID in Gender. And total Weight = 1 for all ID in Class.

Each ID has Weight between 0 to 1, and the weight figuare is same for Gender and Class.

My problem: How can I decompose different class according to Gender?

i.e. for Class 1, it's weight is 0.2, and I want a table to show that that 0.2 made up by 0.1 of Males and 0.05 of Famales and 0.05 and Not disclosed.

All data are time series. Therefore, the table I want will be:

Col1: Date,

Col2:Class,

Col3:weight of Class,

Col4: Weight of Class contributed by Males

Col5: Weight of Class contributed by Females

Col6:Weight of Class contributed by Not Disclose.

Any thought?

Regards,

Ivan

[johnw]

OK, so you're using a generic database structure with field:value pairs? Something like this?

LOAD * INLINE [
ID ,Field ,Value ,Weight
1 ,Gender ,Male ,.5
1 ,Class ,3 ,.7
2 ,Gender ,Female ,.1
2 ,Class ,3 ,.2
3 ,Gender ,Not Disclosed ,.4
3 ,Class ,3 ,.1
];

I'm not sure where date fits in, so I'm ignoring that.

I'd personally get rid of the field:value pairs and just make one row per ID with fields "Gender", "Class", "Gender Weight" and "Class Weight". But you don't have to, of course. Here's one approach:

Dimension = if(Field='Class',Value)
Expression 1 = sum({<Field={'Class'}>} Weight)
Expression 2 = sum({<Field={'Class'},ID=P({<Field={'Gender'},Value={'Male'}>})>} Weight)
Expression 2 = sum({<Field={'Class'},ID=P({<Field={'Gender'},Value={'Female'}>})>} Weight)
Expression 2 = sum({<Field={'Class'},ID=P({<Field={'Gender'},Value={'Not Disclosed'}>})>} Weight)

[#3]

Hi John,

Thanks for your suggestion. I am away from the office and will test it later.

Manay thanks,

Ivan