Condition in set analysis.

[VamsiBhagavathula1]

Hello Guys,

Could someone please tell me how to do a field comparison inside set analysis based on the Qlik calendar?

in my case, one customer has multiple refunds for a order on different dates. Qlik calendar is giving me multiple rows for the refund dates(like cross join) . Out of this i need only the rows where calendar date matches refund date.  After this , I need to make some set analysis.

So, mainly, I want to achieve this Date comparison of calendar date & refund date in set analysis and perform count & sum aggregations.

Please see the screenshot attached file of QVF & screenshot. Please let  me know  how  we can achieve it set analysis.

Thanks,

Vamsi

[VamsiBhagavathula1]

@vinieme12 : Date comes from canonical calendar only. But how could we fix this in Qlik calendar script to avoid multiple rows. may be you could use my qvf file attached

[vinieme12 · Accepted solution]

You need to use a composite key, refer below

///****DATA SECTION****//

Orders:
LOAD *,C_ID&OrderDate as cid_date_key INLINE [
C_ID, OrderDate, AMT
ABC, 2023-01-23, 50
DEF,2023-02-02,60

]
;

REFUNDS:
LOAD *,C_ID&RefundDate as cid_date_key INLINE [
C_ID, RefundDate,REASON,REF_AMT
ABC, 2023-02-01,PART1,10
ABC, 2023-03-10,PART2,20
ABC, 2024-04-01,PART3,10
DEF,2023-04-21,FULL,20

]
;

///****Common Calendar  SECTION****//

TRACE ------------------------------------------------;
TRACE Creating Calendar;
TRACE ------------------------------------------------;

/* Create Custom Calendars per Fact Table*/

Unqualify *;

ORDER_CALENDAR:
Load
cid_date_key,
OrderDate AS Date,
'ORDERS' AS DateType
Resident Orders;

REFUND_CALENDAR:
Load
cid_date_key,
RefundDate as Date,
'REFUNDS' as DateType

//,REASON
Resident REFUNDS;

/* Create a Common Calendar to allow filtering all data at once*/
Call Calendar('Date', 'E');

Measure highlighted

Count({< DateType={'REFUNDS'}>} REF_AMT)

[VamsiBhagavathula1 · Accepted solution]

Hi @vinieme12 , The key approach fails as the individual calendars couldn't to their respective fact tables.

cid_date_key is not the common key between Orders & refunds. So its creating another cross join.

[VamsiBhagavathula1]

@BrunPierre : can we some how  fix  this dates in Calendar script ??

[marcus_sommer]

Just a few hints.

By the given data-structure the query isn't solvable with a set analysis else it requires always an if-loop. Therefore you may use one like above - not only to return the date else also as a condition for all other expressions.

Probably better would be you follows the suggestion from @BrunPierre to create appropriate flags within the script. That the dates are coming originally from different tables is no obstacle because you may use a mapping for it, for example something in this way:

m: mapping load Key & RefundDate, 1 from Refund;

...
applymap('m', Key & Date, 0) as Flag,
... 

[VamsiBhagavathula1]

@marcus_sommer : idea of creating flags is good but i cannot create it because in refunds table i only have refundsdate, The other Date is coming from Qlik Torbien calendar script. So how could we create the flag ??

Thanks

Vamsi

[marcus_sommer]

The calendar(s) are just extra dimension-table(s) to simplify the creation of period-fields. This means they are nor relevant for this kind of transformation respectively matching else the flag-creation based on the fact-tables.

To be more specific you need to create the mapping-table from the refund-source before you load the other fact-table in which you include the applymap().

[VamsiBhagavathula1]

@marcus_sommer yes i understood that but in this scenario i have only C_ID field which determins order date and refund dates. Any ideas/advise how could we create a mapping table in between??

[marcus_sommer]

Just try it like above described and instead of Key in the example you use C_ID. Depending on the values within the ID you may need to add a delimiter between the ID & '|' & Date and also formatting the date on all sides equally.

[vinieme12]

Works fine in the sample you attached, refer screenshot