topic Re: How to Splitting Data in QlikView

How to Splitting Data

Tue, 20 Sep 2011 00:36:55 GMT

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

How to Splitting Data

Tue, 20 Sep 2011 01:18:27 GMT

Hi Vicky,

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

Regards,

Kiran

Re: How to Splitting Data

jykang0638 — Tue, 20 Sep 2011 05:27:33 GMT

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.

Re: How to Splitting Data

llauses243 — Tue, 20 Sep 2011 09:04:58 GMT

Hi jykang0638,

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

Good luck,Luis

Re: How to Splitting Data

rohit214 — Tue, 20 Sep 2011 09:40:56 GMT

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

Re: How to Splitting Data

jykang0638 — Tue, 20 Sep 2011 11:18:58 GMT

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

I modified my original post.

Re: How to Splitting Data

rohit214 — Tue, 20 Sep 2011 11:43:37 GMT

thanks for reply

regards

rohit

Re: How to Splitting Data

Tue, 20 Sep 2011 20:28:08 GMT

Thanks everyone who responded!!!

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

Re: How to Splitting Data

stevedark — Tue, 20 Sep 2011 21:41:05 GMT

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

Re: How to Splitting Data

jykang0638 — Wed, 21 Sep 2011 00:49:03 GMT

Thanks Setve!

Your recommandation is very effective and better performance.

Re: How to Splitting Data

stevedark — Wed, 02 May 2012 19:23:38 GMT

Hi there - I have uploaded the solution as a shared QlikView, and this can be downloaded from here:

QlikView App: Split Values from a String and ApplyMap to Results

This version is Personal Edition compatible.

Steve