Maximum Length of row in a column

[achitreskyitgroup]

Hello,

How can we find the max len of the data in each column?

Example:

tbl:

LOAD * INLINE [

    Year, Week, Purchase

    2012, 21, 15000

    2012, 22, 24000

    2012, 33, 55000

    2013, 1, 15000

    2013, 2, 24000

    2013, 3, 16000

    2013, 24, 103560

    2013, 25, 563200

];

Should give me

$ Field              Max length

Purchase                  6

Week                         2

Year                           4

[DavidM]

If you want table exactly like you wanted you need to do:

Table:

Load

'Purchase' as Field

Max(Len(Purchase) as MaxLenght

Resident tbl;

concatenate (Table)

Load

'Week' as Field

Max(Len(Week) as MaxLenght

Resident tbl;

concatenate (Table)

Load

'Year' as Field

Max(Len(Year) as MaxLenght

Resident tbl;

[achitreskyitgroup]

Hello David, thank you so much for your reply.

I want to do it in a pivot table. There are 68 fields for which I want to find the max len of row.

[DavidM]

Are all fields in one table?

[achitreskyitgroup]

Yes

[DavidM]

I think you can do it with usage of system fields $table and $field.

Or you can create a new table, load the names of the fields with FieldName() and then with iteration you could assign the field name to variable and load each time max and join it to one table.

[achitreskyitgroup]

I found a qlikview file with this expression. It works fine there but the same expression does not work in any other Qv document

-

pick(wildMatch(Only([$Field]),SubField('$(vvv)','|',1),SubField('$(vvv)','|',2),SubField('$(vvv)','|',3))
,max(len($(=SubField('$(vvv)','|',1))))
,max(len($(=SubField('$(vvv)','|',2))))
,max(len($(=SubField('$(vvv)','|',3))))
)

[Brett_Bleess]

Abhishek, Check out the Governance Dashboard application, that may have what you need, or give you some further ideas on how to go about things.

https://help.qlik.com/en-US/governance-dashboard/Content/What.htm

You can download the actual app from the download site, Choose QlikView product area and then look for the Governance Dashboard in the Product list box there.  Sorry I do not have anything better.

The only other place I could recommend to search would be the Design Blog area:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

There are lots of posts out there on how-to, so you may find something there that helps as well.

My post will also kick this back up in the list, so someone else may see things and chime in too.

Regards,
Brett

[rwunderlich]

Dimension:
$Field

Expression: 
pick(
FieldIndex('$Field', [$Field])
,$(=concat({1} DISTINCT  'max(len([' & [$Field] & ']))', ',', FieldIndex('$Field', [$Field])))
)

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com