topic Re: Subfield in QlikView

Subfield

nareshthavidishetty — Mon, 28 Dec 2015 11:26:01 GMT

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

Re: Subfield

sunny_talwar — Mon, 28 Dec 2015 11:29:23 GMT

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

I would try like this:

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

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

Re: Subfield

nareshthavidishetty — Mon, 28 Dec 2015 11:39:08 GMT

thanks sunny i tried what u have given but no luck

Re: Subfield

sunny_talwar — Mon, 28 Dec 2015 11:41:31 GMT

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

Re: Subfield

PrashantSangle — Mon, 28 Dec 2015 11:47:24 GMT

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

Re: Subfield

nareshthavidishetty — Mon, 28 Dec 2015 11:47:51 GMT

im attaching sample data here jus have a look

Re: Subfield

nareshthavidishetty — Mon, 28 Dec 2015 11:49:25 GMT

thanks for the reply ,they are all columns containing values

Re: Subfield

nareshthavidishetty — Mon, 28 Dec 2015 11:57:57 GMT

thanks sunny i got that one with ur expression above mentioned

Re: Subfield

sunny_talwar — Mon, 28 Dec 2015 11:59:09 GMT

Awesome

Re: Subfield

MayilVahanan — Mon, 28 Dec 2015 12:05:15 GMT

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

Re: Subfield

MayilVahanan — Mon, 28 Dec 2015 12:06:46 GMT

Got answer ?? Awesome

Credits goes to sunindia

Re: Subfield

sunny_talwar — Mon, 28 Dec 2015 12:09:20 GMT

Hahahaha, I can't really say how my solution worked for his scenario, but it seems that it somehow did .

Re: Subfield

nareshthavidishetty — Mon, 28 Dec 2015 12:40:41 GMT

thanks for the reply as im trying ur code mentioned above could u help me to understand little bit more please dont mind

Re: Subfield

nareshthavidishetty — Mon, 28 Dec 2015 12:56:35 GMT

CrossTable(FieldName, FieldValue, 😎 please explain these step in cross table as im bit unable to understand the crosstable

what if i got more columns so how should i need to specify parameters

Re: Subfield

MayilVahanan — Tue, 29 Dec 2015 04:36:40 GMT

Hi Naresh,

third parameters indicates from which field you need to be converted to generic.

In this example, from 8 field. So I mentioned as 8.

From help

crosstable (attribute field name, data field name [ , n ] ) ( loadstatement | selectstatement )

where:

attribute field name is the field to contain the attribute values.

data field name is the field to contain the data values.

n is the number of qualifier fields preceding the table to be transformed to generic form. Default is 1.