topic Filter transformation on a resident table in QlikView

Filter transformation on a resident table

Wed, 13 Apr 2011 15:07:58 GMT

Hi,

Is it possible to do a filter transformation on a resident table?

When you load an Excel file through the wizard, you get the "Enable Transformation Step" option. When you finish the wizard it puts something like this at the end of your statement:

(biff, embedded labels, table is Sheet1$, filters(Replace(2, bottom, StrCnd(null))))

I want to do just this, but then to a resident table. If I would put it in pseudo code it would look something like this:

LOAD *
RESIDENT(MyTable)
(filters(Replace(1, bottom, StrCnd(null))));

Or directly from the SQL:

LOAD *;
SQL SELECT * FROM MyTable
(filters(Replace(1, bottom, StrCnd(null))));

I can of course store the file in a QVD and then load it as a table file. This does allow me to use the transformation options. Is this the only way or is it possible to do it directly?

Thanks!

Filter transformation on a resident table

Miguel_Angel_Baeyens — Wed, 13 Apr 2011 15:16:17 GMT

Hello,

Filters() are only appllied to text files or QVD files when using the transformation step. Since you are loading from a file, and unless there is no other way to do that, I'd use any QlikView function in the LOAD part of the script.

The SELECT part is not bound to work with "filters()" since it's a QlikView function, not a SQL database function, so in this part only SQL statements that would work in your query analyzer will be accepted (besides the driver constraints).

What do you want to get exactly?

Regards.

Filter transformation on a resident table

Wed, 13 Apr 2011 15:32:58 GMT

Take the two tables in the image below:

Table 1 is what I have and Table 2 is what I want. If you would have this in an Excel file you could easily use the fill. It looks like I have to do some fancy SQL stuff to make it come out like table 2.

Filter transformation on a resident table

Wed, 13 Apr 2011 15:40:27 GMT

I think you should see this

http://community.qlik.com/wikis/qlikview-wiki/using-peek-and-previous-functions-recursively-to-help-transform-data.aspx

Filter transformation on a resident table

Miguel_Angel_Baeyens — Wed, 13 Apr 2011 15:56:10 GMT

Hello,

Check the following example:

Data:LOAD * INLINE [Datekey, Period201101012011020120110301, 1201104012011050120110601, 2]; Step1:NOCONCATENATE LOAD *RESIDENT DataORDER BY Datekey DESC; DROP TABLE Data; Step2:NOCONCATENATE LOAD Datekey, If(Len(Period) = 0, Peek('Period'), Period) AS PeriodRESIDENT Step1; DROP TABLE Step1;

Anyway, I'm assuming that that data is date related, so I'd use a function to get the quarter depending on the date:

Data:LOAD Date(Date#(Datekey, 'YYYYMMDD')) AS Datekey, Div(Month(Date(Date#(Datekey, 'YYYYMMDD'))) +2, 3) AS Period INLINE [Datekey201101012011020120110301201104012011050120110601];

Even if that's the data, a string function.

Hope that helps.

Filter transformation on a resident table

Wed, 13 Apr 2011 16:42:36 GMT

@Muncho: Thanks that does look like a way to go if you have no field that implies any order. I'd still have to change it a bit because this fills the values above. I have to fill the values below.

@Miguel: Thanks for your replies. Your sample code works like a charm. Can you elaborate on the use of the peek function? It is quite clear what it does when you call the function with all three parameters. But what happens here, when calling it with only one? Howcome it contains the next period?

Filter transformation on a resident table

Miguel_Angel_Baeyens — Wed, 13 Apr 2011 19:52:18 GMT

Hello,

When Peek() is called without the table parameter, nor the index number, it takes the last loaded value for the field specified. In this case, is exactly what I'm looking for, that is, the last value for Period of the current table, even when it is being loaded.

There's no "next period" actually, because the next record is never loaded yet, that's why I'm ordering the table in descending order (Step1) so you know the previous (last loaded) value of Period.

Glad to help.

Filter transformation on a resident table

Thu, 14 Apr 2011 12:49:17 GMT

I did not know peek could be used in this way: powerfull function!

In your second code block you suggest deriving the period from the date. This does seem logical, but in my case the period cannot be extracted from the date.

Thanks for your help!