CROSS APPLY SQL FUNCTION

[farolito20]

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

[Anonymous]

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?

[farolito20]

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

[hic]

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

[Anonymous]

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.

[hic]

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

[richard]

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

TabelA:

1,

2

From SQLTableA;

left join (TableA)

TabelB:

3

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

1,

2,

3

resident TableA

Where ......;