Discussion Board for collaboration related to QlikView App Development.
I want to load a few Census data CSV.
The files contain data from several different years worth of data with the census year as pat of the field header
" Load Area_Code_and_Description,
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);"
Is it possible to load the first three fields and then select only data with field headers that include "2013".
Manual option available but would like to avoid.
Maybe you want to use Crosstable in the script? like:
CrossTable(YearAndType, Value, 3)
This way it turns your field headers into field values under the field "YearAndType" and the values for those fields into "Value".
Then you can just make a selection on your field "YearAndType".
Choose whatever names you want for YearAndType and Value.
Hope this helps!
At first question you can try with 'where' clause in the load statement. Or you can use options in "Enable Transformation Step" from "Table Files Load".
For the next problem you can try with 'Text Between' function. You have to separate first part of the field. 'Help' in qlkview is good option to understand how to do this.
Now I'm reading my previous post and maybe is a little bit mess .
First you can use the "Enable Transformation Step" - Crosstable option to create headers like values.
Next use 'Text Between' function to separate first string from the values and put the results value in new field for example Year. Something like that:
left(textbetween [2001_Census_occupied_private_dwelling_type_Separate_House], ........) )as Year,
And In the end you can use where clause or match function to filter the data in the application.
You can try adding filters and transform your data coming in like:
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, filters(
RowCnd(CellValue, 1, StrCnd(equal, 'Area_Code_and_Description', not)),
RowCnd(CellValue, 1, StrCnd(equal, 'Code', not)),
RowCnd(CellValue, 1, StrCnd(equal, 'Description', not)),
RowCnd(CellValue, 1, StrCnd(contain, '2013', not)))),
However, I am not sure how much of a performance hit this will take. This will remove any fields that is not Area_Code_and_Description, Code, or Description and doesnt contain 2013 in their names.
Hope this helps!