Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
farolito20
Contributor III
Contributor III

CROSS APPLY SQL FUNCTION

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

6 Replies
kmcgovern1
Creator
Creator

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
Contributor III
Contributor III
Author

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

Henric_Cronström

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

kmcgovern1
Creator
Creator

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.

Henric_Cronström

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
Partner
Partner

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 ......;