Please look at attached application. There's two tables loaded with no common field name, but I want the fields "CustomerId" and "cid" to be associated by a set analysis statement.
In the attached application, I have made a table chart with an expression with an If statement, and I want to make an expression with a set statement that calculates the same numbers in that table chart.
How do I write that set statement? Is it possible?
Well, the performance is likely to be much worse relating fields in your charts with if() than simply relating them in your data model. I think your answer is what you already stated, "Else I have to come up with a DW solution before I load anything into QlikView". Mind you, you don't need a full data warehouse before you can start on a QlikView application. But I do think you want to solve this problem in your data model, not in your chart.
I'm not sure if this is applicable, but I'll outline our basic technique in our shop. Our actual data loads from the databases are done in separate QVWs that create QVDs. The user applications simply load from these QVDs. When loading from a QVD, if performance is an issue, you need to make sure you're getting an "optimized load". For an optimized load, your where expression can have no more than a single exists() and no other conditions, and you can't have any expressions in your list of fields (though you can rename them). A typical approach for me might be:
[My Table]: LOAD * INLINE [ Some Field Value 1 Value 2 Value 3 ]; INNER JOIN ([My Table]) LOAD "Key Field" ,"Some Field" ,"Some Other Field" ,"Field 3" as "Cost" ,"Field 4" as "Weight" ,"Field 5" as "Revenue" FROM My_Table.qvd (QVD) WHERE exists("Some Field") ; INNER JOIN ([My Table]) LOAD * INLINE [ Some Other Field A B C ]; LEFT JOIN ([My Table]) LOAD "Key Field" ,if("Weight">5,'Y','N') as "Heavy?" ,"Revenue" - "Cost" as "Margin" RESIDENT [My Table] ;
It's a lot more trouble than how we might normally write this, but it can be worth it when load performance is a problem. It may also be worth adding common "calculated fields" to the QVD to save the last step. Also, if you need the same data in more than one user application, you don't want to have to maintain these calculations in more than one place.
