Formatting

[soha1902]

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.

[avinashelite]

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

[sunny_talwar]

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)

[sunny_talwar]

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)

[soha1902]

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.

[sunny_talwar · Accepted solution]

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

[#7]

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))

[awhitfield]

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

Andy

[soha1902]

Hi Sunny,

Once again thanks a lot .

[sunny_talwar]

I like this solution better than mine. Much more elegant

Best,

Sunny