Load with criteria on field header

[#1]

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

[jerem1234]

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!

[#3]

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.

[#4]

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

[jerem1234]

What do you mean? Do you mean you need to export a straight or pivot table out of Qlikview into excel?

[#6]

More an ETL by left joining 7 files and creating a single csv

[#7]

Thanks

How do I reference the header (and not the data) in the 'where' clause or 'text between'

[#8]

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

[jerem1234]

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!