topic Re: Determine Longest Field Size in QlikView

Determine Longest Field Size

haymarketpaul — Sat, 16 Nov 2024 17:35:43 GMT

Morning All

Qlikview 12 SR5

I've got a csv file with about 300 fields in it and approx 1 million rows.

For each field I need to work out the maximum size/length of the data in it.

Here's a simple example...

Field1	Field2
Paul	Red
Harry	Blue
Robert	Green
Dave	Grey
Dan	Pink

So looking at the above Field1 would be 6 (Robert) and Field2 would be 5 (Green)

So I just need to list of the max size of these 300 fields

Field1	6
Field2	5

etc

I ultimately need to create some SQL Server tables for this data so want to use this info to determine field sizes in that

Re: Determine Longest Field Size

Frank_Hartmann — Thu, 21 Jan 2021 10:08:49 GMT

Maybe like this?

tmp: Load * Inline [ Field1, Field2 Paul, Red Harry, Blue Robert, Green Dave, Grey Dan, Pink ]; left join Load max(len(Field1)) as Max1, max(len(Field2)) as Max2 Resident tmp;

Re: Determine Longest Field Size

marcus_sommer — Thu, 21 Jan 2021 10:29:12 GMT

I think I would do something like this:

csv: load * from csv.csv;

for i = 1 to nooffields('csv')
let f = fieldname($(i), 'csv');
t: load '$(f)' as Field, max(len(fieldvalue('$(f)', recno()))) as MaxLenght
autogenerate fieldvaluecount('$(f)');
next

- Marcus

Re: Determine Longest Field Size

haymarketpaul — Thu, 21 Jan 2021 11:00:49 GMT

Brilliant - thank you

and wow 212 chars in the postcode field - someone must live somewhere odd 🙂