topic Re: Using a loop to load multiple scores for each record in QlikView

Using a loop to load multiple scores for each record

Anonymous — Thu, 16 Jul 2015 15:43:41 GMT

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?

Re: Using a loop to load multiple scores for each record

hic — Thu, 16 Jul 2015 16:43:54 GMT

Isn't this just a crosstable? The Crosstable Load

HIC

Re: Using a loop to load multiple scores for each record

Peter_Cammaert — Thu, 16 Jul 2015 16:51:01 GMT

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

Re: Using a loop to load multiple scores for each record

Anonymous — Fri, 17 Jul 2015 07:53:51 GMT

I removed the "1, " and it worked perfectly. Thanks!

Re: Using a loop to load multiple scores for each record

sasiparupudi1 — Fri, 17 Jul 2015 08:40:14 GMT

Please close the thread by marking the correct answer