Hello,
I am building a qlikview with fairly complicated expressions in the charts and as sheet variables. These expressions are often comprised of nested if statements in order to account for the various combinations that can occur. The expressions were working, but as they become more complicated, it is clear that the qlikview app cannot handle the calculations in the charts, which is leading me to see if it is possible to add them to the load script.
However, the expressions that I need reference fields that are located in 4 different tables. For instance:
(if([Discount 14A] = 0, 0,
if([Discount 14 List Category]=0,
if([Discount 14 Minimum Order]=0 and [Discount 14 Maximum Order]=0,
SUM(AGGR(SUM(DISTINCT POCost*Quantity), ProjectItemID)) ,
if([Discount 14 Minimum Order]=0 and [Discount 14 Maximum Order]>0,
SUM(if(PurchaseOrderTotal>= 0 and PurchaseOrderTotal<= [Discount 14 Maximum Order],AGGR(SUM(DISTINCT POCost*Quantity), ProjectItemID)) ),
if([Discount 14 Minimum Order]>0 and [Discount 14 Maximum Order]=0,
SUM(if(PurchaseOrderTotal>=[Discount 14 Minimum Order], AGGR(SUM(DISTINCT POCost*Quantity), ProjectItemID))),
if([Discount 14 Minimum Order]>0 and [Discount 14 Maximum Order]>0,
SUM(if(PurchaseOrderTotal>= [Discount 14 Minimum Order] and PurchaseOrderTotal <= [Discount 14 Maximum Order], AGGR(SUM(DISTINCT POCost*Quantity), ProjectItemID))))))),
if([Discount 14 List Category]>0,
if([Discount 14 Minimum Order]=0 and [Discount 14 Maximum Order]=0,
SUM(AGGR(SUM(DISTINCT if([Discount 14 List Category]=DiscountSkuListCategoryID, POCost*Quantity)), ProjectItemID)) ,
if([Discount 14 Minimum Order]=0 and [Discount 14 Maximum Order]>0,
SUM(AGGR(SUM(DISTINCT if(PurchaseOrderTotal>=0 and PurchaseOrderTotal<= [Discount 14 Maximum Order] and [Discount 14 List Category]=DiscountSkuListCategoryID, [Item Sell Price Total])), ProjectItemID)) ,
if([Discount 14 Minimum Order]>0 and [Discount 14 Maximum Order]=0,
SUM(AGGR(SUM(DISTINCT if(PurchaseOrderTotal>=[Discount 14 Minimum Order] and [Discount 14 List Category]=DiscountSkuListCategoryID, [Item Sell Price Total])), ProjectItemID)) ,
if([Discount 14 Minimum Order]>0 and [Discount 14 Maximum Order]>0,
SUM(AGGR(SUM(DISTINCT if(PurchaseOrderTotal>= [Discount 14 Minimum Order] and PurchaseOrderTotal<= [Discount 14 Maximum Order] and [Discount 14 List Category]=DiscountSkuListCategoryID, [Item Sell Price Total])), ProjectItemID)) ))))))
)
/
SUM(AGGR(SUM(DISTINCT POCost*Quantity), ProjectItemID)) )
this reference Discounts which are located in one table, Product information located in a separate table, Purchase Order information in a third table, and Sku information listed in a fourth table.
I tried to create a qvd with all of these tables joined in order to have them in one table, but that then destroyed my computer's memory (16 gigs) and would not complete. I tried both outer and left joins, but do not want to use an inner join, as it will eliminate some of my data.
I have found some information that suggests using a mapping function in the script, but from what I can tell, this only allows you to join 2 tables, not 4.
Any suggestions / help is appreciated!
