topic Re: Formatting in QlikView

Formatting

soha1902 — Tue, 12 May 2015 10:26:34 GMT

Hi All,

I have one field name "NewField" .

If I show this field in Test Object its look like this:

Now My requirement is I want to show these values with following formatting in Text Object:

Name Hour

A 10

A 20

B 20

B 25

C 15

Please help.

Thanks.

Re: Formatting

avinashelite — Tue, 12 May 2015 10:31:19 GMT

Hi Soha,

Try like this in the script:

Load *,

subfield(subfield(NewField,','),'-',1) as Name,

subfield(subfield(NewField,','),'-',2) as Hours

from

....

I don't think this can be presented in Test box, use chart or table box

Re: Formatting

sunny_talwar — Tue, 12 May 2015 10:42:04 GMT

Try this in a text object:

=SubField(SubField(Newfield2, ',', 1), '-', 1) & Chr(32) & Chr(32) & SubField(SubField(Newfield2, ',', 1), '-', 2) & Chr(13) &

SubField(SubField(Newfield2, ',', 2), '-', 1) & Chr(32) & Chr(32) & SubField(SubField(Newfield2, ',', 2), '-', 2) & Chr(13) &

SubField(SubField(Newfield2, ',', 3), '-', 1) & Chr(32) & Chr(32) & SubField(SubField(Newfield2, ',', 3), '-', 2) & Chr(13) &

SubField(SubField(Newfield2, ',', 4), '-', 1) & Chr(32) & Chr(32) & SubField(SubField(Newfield2, ',', 4), '-', 2) & Chr(13) &

SubField(SubField(Newfield2, ',', 5), '-', 1) & Chr(32) & Chr(32) & SubField(SubField(Newfield2, ',', 5), '-', 2) & Chr(13)

Re: Formatting

sunny_talwar — Tue, 12 May 2015 10:44:34 GMT

To fix the formatting and getting the headers back you can use the following expression:

='Name' & Chr(32) & Chr(32) & 'Hours' & Chr(13) &

SubField(SubField(Newfield2, ',', 1), '-', 1) & Chr(32) & Chr(32) & Chr(32) & Chr(32) & Chr(32) & Chr(32) & SubField(SubField(Newfield2, ',', 1), '-', 2) & Chr(13) &

SubField(SubField(Newfield2, ',', 2), '-', 1) & Chr(32) & Chr(32) & Chr(32) & Chr(32) & Chr(32) & Chr(32) & SubField(SubField(Newfield2, ',', 2), '-', 2) & Chr(13) &

SubField(SubField(Newfield2, ',', 3), '-', 1) & Chr(32) & Chr(32) & Chr(32) & Chr(32) & Chr(32) & Chr(32) & SubField(SubField(Newfield2, ',', 3), '-', 2) & Chr(13) &

SubField(SubField(Newfield2, ',', 4), '-', 1) & Chr(32) & Chr(32) & Chr(32) & Chr(32) & Chr(32) & Chr(32) & SubField(SubField(Newfield2, ',', 4), '-', 2) & Chr(13) &

SubField(SubField(Newfield2, ',', 5), '-', 1) & Chr(32) & Chr(32) & Chr(32) & Chr(32) & Chr(32) & Chr(32) & SubField(SubField(Newfield2, ',', 5), '-', 2) & Chr(13)

Re: Formatting

soha1902 — Tue, 12 May 2015 10:50:52 GMT

Hi SunIndia,

First of all thanks a lot for your help. Your solution is correct and worknig as expected. Only one thing is required.

I posted a sample application with dummy data. Original application having N number of record so how we can got this dynamically?

Thanks.

Re: Formatting

sunny_talwar — Tue, 12 May 2015 10:56:35 GMT

Try this:

='Name' & Chr(32) & Chr(32) & 'Hours' & Chr(13) &

Concat(SubField(SubField(Newfield2, ',', ValueLoop(1, 5, 1)), '-', 1) & Chr(32) & Chr(32) & Chr(32) & Chr(32) & Chr(32) & Chr(32) &

SubField(SubField(Newfield2, ',', ValueLoop(1, 5, 1)), '-', 2), Chr(13))

Here you will need to put number of rows of data have for Name and Hours within ValueLoop(1, 5, 1)

HTH

Best,

Sunny

Re: Formatting

Tue, 12 May 2015 11:00:32 GMT

It also can be done with something like

=replace(replace('Name' & Chr(9) & Chr(9) & 'Hour' & Chr(13) & 'A-10,B-20,C-30',',',Chr(13)),'-',Chr(9) & Chr(9))

Re: Formatting

awhitfield — Tue, 12 May 2015 11:02:15 GMT

You Had those values already loaded in your other post, but then you dropped the table with them in !!

Andy

Re: Formatting

soha1902 — Tue, 12 May 2015 11:04:40 GMT

Hi Sunny,

Once again thanks a lot .

Re: Formatting

sunny_talwar — Tue, 12 May 2015 11:07:38 GMT

I like this solution better than mine. Much more elegant

Best,

Sunny

Re: Formatting

sunny_talwar — Tue, 12 May 2015 11:08:23 GMT

No problem once again. But do look at Imad Zidan‌'s expression... Its is more elegant than what I proposed.

Best,

Sunny