topic Re: CROSS APPLY SQL FUNCTION in QlikView

CROSS APPLY SQL FUNCTION

farolito20 — Tue, 22 Jan 2013 18:41:46 GMT

How to do a CROSS APPLY (sql function) in qlikview, using resident tables or qvd?

Re: CROSS APPLY SQL FUNCTION

Anonymous — Tue, 22 Jan 2013 18:48:52 GMT

Without the context of what you're trying to accomplish, I'm not sure I can give you an in depth answer. But I would look into the ApplyMap function and see if that answers your question. If not, can you give some more details of what you're looking to do, possibly providing an example as well?

Re: CROSS APPLY SQL FUNCTION

farolito20 — Tue, 22 Jan 2013 18:52:43 GMT

I have a table1 with codContract, Month, Year, Active

An other table2 with the mostRecent Month, Year to codContract.

So, if Active=0 I need to replace my Month.Year field from table1 with Month, Year from table2

Re: CROSS APPLY SQL FUNCTION

hic — Tue, 22 Jan 2013 19:07:48 GMT

There is no simple way to take QlikView data (resident tables of qvd), push it back to the RDMBS and make a CROSS APPLY there. (You can make some kludgy solutions pushing data back to the DB using macros in QlikView, but I wouldn't recommend it.)

Having said that, I would like to know why you would want to use a CROSS APPLY. I have not yet found a JOIN/APPLY data modelling problem that cannot be solved by the internal QlikView functions and table operators, such as Join, Keep, Intervalmatch and Applymap().

See for instance http://community.qlik.com/blogs/qlikviewdesignblog/2012/09/18/one-favorite-function-applymap

HIC

Re: CROSS APPLY SQL FUNCTION

Anonymous — Tue, 22 Jan 2013 19:17:53 GMT

You can probably use IF statements on the Month and Year fields in Table1 to return the most recent Month and Year using the Active field as a condition. You may have to convert the Month and Year fields into variables though. There probably is a more eloquent use but this is quick and dirty.

Re: CROSS APPLY SQL FUNCTION

hic — Tue, 22 Jan 2013 19:22:50 GMT

Try

Table2Map:

Mapping Load

codContract, MakeDate(Year,Month);

SQL SELECT codContract, Year, Month FROM table2

Table1:

Load

codContract,

YearMonth,

Month(YearMonth) as Month,

Year(YearMonth) as Year,

Active;

Load

codContract, Month, Year, Active,

if(Active=0,Applymap('Table2Map',MakeDate(Year,Month)),MakeDate(Year,Month)) as YearMonth;

SQL SELECT * FROM table1;

HIC

Re: CROSS APPLY SQL FUNCTION

richard — Mon, 01 May 2017 09:58:12 GMT

This is just like a join on no fields in both tables. it should look like this

TabelA:

From SQLTableA;

left join (TableA)

TabelB:

From SQLTableB;

TableC: //This table is like using the cross apply because this is where you use the where conditions to reduce the dataset.

Load

resident TableA

Where ......;