Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi have an Excel database with the following columns:
RecordID; {ScoreA}; {ScoreB}; {ScoreC}; ....
The {ScoreA}, {ScoreB} etc will be the description of the scores with the score values in each column and won't be 'ScoreA', 'ScoreB', etc.
I want to load the data so that for each Record ID, the score value is linked to the score description. I can use:
LOAD RecordID,
{ScoreA} as score,
'ScoreA' as ScoreType
FROM ...
LOAD RecordID,
{ScoreB} as score,
'ScoreB' as ScoreType
FROM ...
...
How do I use a loop to load the data more elegantly?
Isn't this just a crosstable? The Crosstable Load
HIC
Or to put it in "code":
Scores:
CROSSTABLE (1, ScoreType, Score)
LOAD RecordID, ScoreA, ScoreB, ScoreC
FROM ...
Now create a pivot table with dimensions RecordID and ScoreType and pivot ScoreType to a horizontal level. Add =Sum(Score) as Expression. Does it look the same? Though the data has now been serialized as you can observe in the Table Viewer (Ctrl-T)
Best,
Peter
I removed the "1, " and it worked perfectly. Thanks!
Please close the thread by marking the correct answer