Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Load with criteria on field header

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

8 Replies
jerem1234
Specialist II
Specialist II

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!

Not applicable
Author

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.

Not applicable
Author

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
Specialist II
Specialist II

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

Not applicable
Author

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

Not applicable
Author

Thanks

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

Not applicable
Author

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
Specialist II
Specialist II

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!