Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have data in a resident table and below is a some sample data, plus the bb.qvw attached has the sample data in it
This loads sample data into SourceTable. In reality there will be a few dozen Flag fields and a few million ID's.
SourceTable:
LOAD * INLINE [
ID, FlagA, FlagB, FlagC
1, Yes, Yes, Yes
2, Yes, Yes, No
3, Yes, No, Yes
4, No, Yes, No
5, No, No, Yes
6, No, No, No
];
This Inline Load shows what I wish this sample data to be transformed into:
WantedTable :
LOAD * INLINE [
ID, FlagName, Value
1, FlagA, Yes
1, FlagB, Yes
1, FlagC, Yes
2, FlagA, Yes
2, FlagB, Yes
2, FlagC, No
3, FlagA, Yes
3, FlagB, No
3, FlagC, Yes
4, FlagA, No
4, FlagB, Yes
4, FlagC, No
5, FlagA, No
5, FlagB, No
5, FlagC, Yes
6, FlagA, No
6, FlagB, No
6, FlagC, No
];
I have gone snowblind bananas trying to work out how to do this, any suggestions would be most welcome.
Best Regards, Bill
For i = 2 to NoOfFields('SourceTable')
let field = FieldName(i,'SourceTable');
Final:
LOAD
ID,
'$(field)' as FlagName,
$(field) as Value
resident SourceTable;
next
For i = 2 to NoOfFields('SourceTable')
let field = FieldName(i,'SourceTable');
Final:
LOAD
ID,
'$(field)' as FlagName,
$(field) as Value
resident SourceTable;
next
Hi Bill
Here we have to use CrossTable to convert l
CrossTable(Flagname,value,1)
LOAD * INLINE [
ID, FlagA, FlagB, FlagC
1, Yes, Yes, Yes
2, Yes, Yes, No
3, Yes, No, Yes
4, No, Yes, No
5, No, No, Yes
6, No, No, No
];
You will get u r desired result
Regards
sasi
Anjos
Many thanks that has cracked it !!
....except that it produce a separate table for each Flag.
So I have added the concatenate and pre-created the table with a Dummy field.
Final:
LOAD * INLINE [
Dummy
Dummy
];
For i = 2 to NoOfFields('SourceTable')
let field = FieldName(i,'SourceTable');
Concatenate ( Final )
LOAD
ID,
'$(field)' as FlagName,
$(field) as Value
resident SourceTable;
next
drop field Dummy;
This works perfectly now, but it seems a little inelegant to have to pre-create the dummy table.
Any suggestions for elegance ?
Best Regards, Bill
Weird... not happened here
But this will fix:
LET v='Final:';
For i = 2 to NoOfFields('SourceTable')
let field = FieldName(i,'SourceTable');
$(v)
LOAD
ID,
'$(field)' as FlagName,
$(field) as Value
resident SourceTable;
if i > 2 then
let v='Concatenate(Final)';
end if;
next
Sasi
Your suggestion also cracks it.
I change it to test it from resident and it handles that fine.
SourceTable:
LOAD * INLINE [
ID, FlagA, FlagB, FlagC
1, Yes, Yes, Yes
2, Yes, Yes, No
3, Yes, No, Yes
4, No, Yes, No
5, No, No, Yes
6, No, No, No
];
OutputTable:
CrossTable(Flagname,value,1)
LOAD *
resident SourceTable
;
And is more elegant.
I'll test both suggestions tomorrow with loads of data..............
Best Regards, Bill