Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to Splitting Data

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


10 Replies
Not applicable
Author

Hi Vicky,

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

Regards,

Kiran

jykang0638
Partner - Contributor III
Partner - Contributor III

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
Creator III
Creator III

Hi  jykang0638,

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

Good luck,Luis

rohit214
Creator III
Creator III

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
Partner - Contributor III
Partner - Contributor III

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

I modified my original post.

rohit214
Creator III
Creator III

thanks for reply

regards

rohit

Not applicable
Author

Thanks everyone who responded!!!

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

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Partner - Contributor III
Partner - Contributor III

Thanks Setve!

Your recommandation is very effective and better performance.