Urgent Help! turn raw table into good table

[#1]

Hello. I am struggling to turn a raw data into result table. I use following script:

load
FORM_ID,
if(QUESTION = 'Placement', ANSWER) as Placement,
if(QUESTION = 'PlacementDate', ANSWER) as PlacementDate,
if(QUESTION = 'Group', ANSWER) as Group,
if(QUESTION_ID = 125, ANSWER) as Product,
if(QUESTION_ID = 126, ANSWER) as Indication,
if(QUESTION_ID = 127, ANSWER) as School;
select * from Rawtable; //Rawtable is coming from SQL datawarehouse

The result is not looking good. What I get is a new row is made when an answer is filled and one additional row with empty answers. I got:

FORM_ID;Placement;PlacementDate;Group;Product;Indication;School
1111;Eindhoven;-;-;-;-;-
1111;-;22-10-2009;-;-;-;-
1111;-;-;Olifant;-;-;-
1111;-;-;-;-;-;-
1122;Utrecht;-;-;-;-;-
1122;-;10-10-2009;-;-;-;-
1122;-;-;Butterfly;;-;-;-
1122;-;-;-;P01;-;-
1122;-;-;-;-;-;-
1123;-;-;-;-;Yes;
1123;-;-;-;-;-;SchoolX
1123;-;-;-;-;-;-

Anyone can help me pls..... thanks!
Cheers,
Upiek

================================================

Rawtable:
ID;QUESTION_ID;QUESTION;ANSWER;FORM_ID
1;122;Placement;Eindhoven;1111
2;123;PlacementDate;22-10-2009;1111
3;124;Group;Olifant;1111
4;122;Placement;Utrecht;1122
5;123;PlacementDate;10-10-2009;1122
6;124;Group;Butterfly;1122
7;125;Product;P01;1122
8;126;Indication;Yes;1123
9;127;School;SchoolX;1123
----------------------------------------------------------------------

Wanted result table:
FORM_ID;Placement;PlacementDate;Group;Product;Indication;School
1111;Eindhoven;22-10-2009;Olifant;-;-;-
1122;Utrecht;10-10-2009;Butterfly;P01;-;-
1123;-;-;-;-;Yes;SchoolX
====================================================

[#2]

One idea since the data in the desired output table seems to be grouped by FORM_ID would be to left join the fields desired to it. E.g.

newTable:LOAD distinct FORM_IDresident RawTable;

now you want to left join the fields you want to create one by one.

left join load FORM_ID, if(QUESTION = 'Placement', ANSWER) as Placement,resident RawTable;left join load FORM_ID, if(QUESTION = 'PlacementDate', ANSWER) as PlacementDate, resident RawTable;

Etcetera... I think that should do it... Haven't tried it. GOod luck! /Fredrik

[disqr_rm · Accepted solution]

See enclosed. This would be the one way to do that. Hope this helps.

[#4]

it works. brilliant sollutions. thanks! i can work on it further.