Subfield

[nareshthavidishetty]

Hi all,

can anyone tell me where im going wrong with the subfield function

i got like these [Gross Sales,April'15,Unit Sales]

                        [free,April'15,Unit Sales]

                 [ salable,April'15,Unit Sales]

so i tried

subfield ( [April'15,',']) as April,

subfield([unit sales,',']) as Unit Sales etc

[sunny_talwar · Accepted solution]

What is the name of the field here????

I would try like this:

SubField(FieldName, ',', 2) as April,

SubField(FieldName, ',', 3) as [United Sales]

[nareshthavidishetty]

thanks sunny i tried what u have given but no luck

[sunny_talwar]

How exactly your data is laid out? Would you be able to share a better sample, preferably from an Excel file?

[PrashantSangle]

Hi,

Your requriement is uncleared can you become more specific???

below field is your column or value

[Gross Sales,April'15,Unit Sales]

[free,April'15,Unit Sales]

[ salable,April'15,Unit Sales]

What you want to acheive from it??

Regards

[nareshthavidishetty]

im attaching sample data here jus have a look

[nareshthavidishetty]

thanks for the reply ,they are  all columns containing values

[nareshthavidishetty]

thanks sunny i got that one with ur expression above mentioned

[sunny_talwar]

Awesome

[MayilVahanan]

HI

Try like this

Test:

CrossTable(FieldName, FieldValue, 😎

LOAD Product,

     PACK,

     [P T S RATE],

     [P T S RATE1],

     [P T S RATE2],

     [P T S RATE3],

     [P T S RATE4],

     [P T S RATE5],

     [Gross Sales,April'15,Unit Sales],

     [Free,April'15,Unit Sales],

     [Salable,April'15,Unit Sales],

     [Non Salable,April'15,Unit Sales],

     [Total,April'15,Unit Sales],

     [Net Units,April'15,Unit Sales],

     [Gross Sales,April'15,Sales-Value],

     [Free,April'15,Sales-Value]

FROM

(ooxml, embedded labels, header is 2 lines, table is Sheet1, filters(

Remove(Row, RowCnd(CellValue, 1, StrCnd(equal, 'Group Total'))),

Remove(Row, Pos(Top, 5)),

Remove(Row, Pos(Top, 4))

));

Final:

LOAD *, SubField(FieldName, ',',1) as Sales,

  SubField(FieldName, ',', 2) as MonthName,

  SubField(FieldName, ',', 3) as Units

Resident Test;

DROP Table Test;

Product	PACK	P T S RATE	P T S RATE1	P T S RATE2	P T S RATE3	P T S RATE4	P T S RATE5	MonthName	Sales	Units	=SUm(FieldValue)
											160862.2
tab1	10's	45.590000						April'15	Gross Sales	Sales-Value	61090.6
tab1	10's	45.590000						April'15	Gross Sales	Unit Sales	1340
tab1	10's	45.590000						April'15	Net Units	Unit Sales	1340
tab2	10's	45.590000						April'15	Gross Sales	Sales-Value	93003.6
tab2	10's	45.590000						April'15	Gross Sales	Unit Sales	2040
tab2	10's	45.590000						April'15	Net Units	Unit Sales	2032
tab2	10's	45.590000						April'15	Non Salable	Unit Sales	8
tab2	10's	45.590000						April'15	Total	Unit Sales	8