Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
How to do a CROSS APPLY (sql function) in qlikview, using resident tables or qvd?
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?
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
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
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.
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
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 ......;