Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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;
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
Brilliant - thank you
and wow 212 chars in the postcode field - someone must live somewhere odd 🙂