Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

QV Scripting, Filtering Binary Load.

Hello. My work is setup so that BI architects setup the QVD and QVW file that loads. Then the Business Analysts create QVWs that Binary Loads from the QVW created by the BI architect. However some of those Data are not needed by me and makes my developing unncessary difficult.

Is there a way to filter some of those data in from my script?

my binary script only has the following:

binary qvw_New_Business_Work_Mgt_Load.qvw;

I am hoping to use where isempty or if([Work Status]<>"")).... (not sure which function is the best to go with any suggestion would be appreciated. Basically i don't need the data where [Work Status] field is empty.)

Thank you QV experts. The community has provided me with some great answers and I am very grateful for such support.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

I don't think you can filter the binary load directly, but you can do a resident load of any table loaded, like

binary qvw_New_Business_Work_Mgt_Load.qvw;

NewFilteredTable:

NOCONCATENATE

LOAD *

RESIDENT TableWithEmptyWorkStatus WHERE len(trim([Work Status]));

DROP TableWithEmptyWorkStatus;

In above script snippet, WHERE len(trim([Work Status])) should only return non-empty work status.

You just need to replace the table name TableWithEmptyWorkStatus with the appropriate table name (ask your BI architect or inspect your tables using CTRL-T table viewer).

This will only filter one table, if you need to remove also records in other tables depending on the filtered table, you may need to do some additional steps (but it's hard to say, which ones. You probably need to describe the model a bit more detailed).

You could also consider creating a new qvw to binary load from by loading the original one, then filter some fields, and using the File->Reduce Data->Keep possible values function from the menu.

Hope this helps,

Stefan

View solution in original post

2 Replies
swuehl
MVP
MVP

I don't think you can filter the binary load directly, but you can do a resident load of any table loaded, like

binary qvw_New_Business_Work_Mgt_Load.qvw;

NewFilteredTable:

NOCONCATENATE

LOAD *

RESIDENT TableWithEmptyWorkStatus WHERE len(trim([Work Status]));

DROP TableWithEmptyWorkStatus;

In above script snippet, WHERE len(trim([Work Status])) should only return non-empty work status.

You just need to replace the table name TableWithEmptyWorkStatus with the appropriate table name (ask your BI architect or inspect your tables using CTRL-T table viewer).

This will only filter one table, if you need to remove also records in other tables depending on the filtered table, you may need to do some additional steps (but it's hard to say, which ones. You probably need to describe the model a bit more detailed).

You could also consider creating a new qvw to binary load from by loading the original one, then filter some fields, and using the File->Reduce Data->Keep possible values function from the menu.

Hope this helps,

Stefan

Not applicable
Author

Hey I know this is late but thanks for your suggestion. It really helped me alot. Much appreciated