Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
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,
Code,
Description,
[2001_Census_occupied_private_dwelling_type_Separate_House],
[2006_Census_occupied_private_dwelling_type_Separate_House],
[2013_Census_occupied_private_dwelling_type_Separate_House],
.,
.,
.
FROM
[XXX]
(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.
Many Thanks
Maybe you want to use Crosstable in the script? like:
CrossTable(YearAndType, Value, 3)
Load .......
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!
Thanks for prompt response.
This would work if the data was to stay in QV , however I need to beable to have a standard table out again.
Hi,
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.
Regards,
Venelin
What do you mean? Do you mean you need to export a straight or pivot table out of Qlikview into excel?
More an ETL by left joining 7 files and creating a single csv
Thanks
How do I reference the header (and not the data) in the 'where' clause or 'text between'
Hi,
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:
[2001_Census_occupied_private_dwelling_type_Separate_House],
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.
Regards,
Venelin
You can try adding filters and transform your data coming in like:
Load
*
From
[XXX]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, filters(
rotate(left),
Remove(Row,RowCnd(Compound,
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)))),
rotate(right)
));
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!