SCRIPT

[upaliwije]

Hi

I have following script in my QV doc

LOAD CLAIM_NO,

     POLICY_NO,

         date(LOSS_DATE,'DD-MMM-YY') AS LOSS_DATE,

         POL_PERIOD_FROM,

      VEH_TYPE,

       DUAL( DATE(POL_PERIOD_FROM,'MMM-YY') &' - '& DATE(POL_PERIOD_TO,'MMM-YY'),POL_PERIOD_FROM)  AS POLICY_PERIOD,

      FROM

[..\Claims.qvd]

(qvd)

CONCATENATE (CLM)

         POLICY_NO,

         BCOD,

         PRD_CODE,

         POLICY_PERIOD,

         PREMIUM,

         DESC,

         RATE,

        AMOUNT,

         FROM

E:\Qlikview\QVD\FACT_Table\Claims\premiumC3.qvd

(qvd)

WHERE EXISTS(POLICY_NO)

I want add a condition where  data from second table should be loaded if

1. Loss date from 1st Table should be between policy period from second table

Pls help

[Oleg_Troyansky]

You can achieve it using Mapping. After loading your first table, create a mapping table that converts the Policy Number to the Claim Date. If you can have multiple dates per policy, you may have to use Min, Max, or both to achieve your goal.

As a second step, you can add a WHERE clause to the second load and specify your condition there, using the ApplyMap() function - something like this:

WHERE

     ApplyMap('Date_Map', POLICY_NO, 0) >= [From Date]

and

     ApplyMap('Date_Map', POLICY_NO, 0) <= [To Date]

;

Keep in mind that this WHERE clause will break the optimization of your QVD load. If you are dealing with large files, you may need to load the whole QVD file and only then apply the condition in the following resident load.

I describe various scenarios of using Mapping in my new book, QlikView Your Business.

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense