How to Splitting Data

[#1]

I´m starting in QV an I´ve a problem...

In a field A I´ve n result of n test, each character represented a value of an examen (between 0 and 5) por example (5412032.....)

and I´ve a another table B

5, excellent

4, very good

3, good

....

0, .....

I need to show Test1: excellent

                        Test2: very good

                     ...........

Any idea?

Thanks

[#2]

Hi Vicky,

Use table box with the fields Tests and Grade Description (Excellent/v good/Good etc)

Regards,

Kiran

[jykang0638]

Hi,

I think you'd better to modify your data model thru scription like the below:

TableA:
Load * Inline [
examen
5012342
3453234
];
TableB:
Load * Inline [
grade, gradenm
5, excellent
4, very good
3, good
2, normal
1, bad
0, worst
];
Let nRows = NoOfRows('TableA');
For i = 0 To $(nRows) - 1
     Let strExamen = peek('examen', $(i), 'TableA');
     Let nLoop = Len($(strExamen));
     For j = 1 To $(nLoop)
          ExamenResult:
          Load 
          $(i) as examSeq
          , $(strExamen) as examen
          , 'Test' & $(j) as testNm
          , Mid($(strExamen), $(j), 1) as grade
          AutoGenerate (1);
     Next j
Next i
Drop table TableA;

I hope it helps you.

Regards,

Steve.

[llauses243]

Hi  jykang0638,

Very good your offer, only must add "Resident ...", plz to see image adjust

Good luck,Luis

[rohit214]

hello,

your programming shows some error .i m new in qlikview.so i don't know where is bug.

please solve thar problem.

thanks

rohit

[jykang0638]

Oh! I was missing AutoGenerate(1); statement at the last of inner loop.

I modified my original post.

[rohit214]

thanks for reply

regards

rohit

[#8]

Thanks everyone who responded!!!

It's works.... but I've 1536 record with 24 test each and need many minutes too load

[stevedark]

Hi Vicky,

You may find you get better performance by substring-ing the results and doing an ApplyMap and then cross-tabling the results.

This script demonstrates the principle:

Map_Result:

MAPPING LOAD

          ID,

          Result

INLINE [

ID,Result

5, Excellent

4, Very Good

3, Good

2, Average

1, Below Average

0, Ungraded

];

TestResults:

CROSSTABLE ([Test Name], Result, 2) LOAD

          [First Name],

          [Last Name],

          ApplyMap('Map_Result', mid(Results,1,1)) as [Test 1],

          ApplyMap('Map_Result', mid(Results,2,1)) as [Test 2],

          ApplyMap('Map_Result', mid(Results,3,1)) as [Test 3],

          ApplyMap('Map_Result', mid(Results,4,1)) as [Test 4],

          ApplyMap('Map_Result', mid(Results,5,1)) as [Test 5]

INLINE [

First Name,Last Name,Results

John,Smith,25141

Jane,Jones,24115

Mike,Cann,42534

Sarah,Samways,12342

];

This can then be adapted to your own data source and expanded up to 25 substrings for your results.

Attached is this code in a QVW file.

For more information on ApplyMap please see my blog post: http://bit.ly/kQcAZ5

- Steve

[jykang0638]

Thanks Setve!

Your recommandation is very effective and better performance.