App Development

[kroberts13]

Hi All,

I am working with a QVD that looks like this:

LOAD

    TX_ID,

    CM_PHY_OWNER_ID,

    CM_LOG_OWNER_ID,

    OLD_ETR_ID,

    REPOSTED_ETR_ID,

    REPOST_TYPE_C,

    IS_PREV_VOID_C

FROM [lib://Transform QVD Path (osumc_helpstar)/ARPB_TX_VOID.qvd]

(qvd);

This is what I am trying to write using the QVD:

,CASE WHEN tdl.DETAIL_TYPE in (1) and (void.OLD_ETR_ID IS NULL AND void.REPOSTED_ETR_ID IS NULL)

THEN

   cast(tdl.[ORIG_POST_DATE] - tdl.[ORIG_SERVICE_DATE] as integer)

END as ORIGINAL_LAG_SERVICE_TO_POST

**DETAIL TYPE AND THE DATES are from a different QVD that I would be Joining to

[ankur_abhishek]

Hi Kyle,

You can use if statement & resident load to get the data from the second QVD.

If(

tdl.DETAIL_TYPE =1 and ISNUll(void.OLD_ETR_ID) AND IsNull(void.REPOSTED_ETR_ID),

   INTERVAL(tdl.[ORIG_POST_DATE] - tdl.[ORIG_SERVICE_DATE] ,'dd') ) as ORIGINAL_LAG_SERVICE_TO_POST

[kroberts13]

I have not used a Resident load in this type of use case. How would it look if I am trying to get the "TDL" fields from this QVD?:

LOAD

    DETAIL_TYPE,

    POST_DATE,

    ORIG_SERVICE_DATE,

    ORIG_POST_DATE,

    TX_ID,

    HAR

FROM [lib://Transform QVD Path (osumc_helpstar)/FactTable.qvd]

(qvd);

[kroberts13]

Any thoughts on this? I really appreciate it

[marcus_sommer]

The general logic will be:

t1:

load Key(s), FurtherFields from QVD;

     join

load Key(s), ExtraFields from OtherSource;

t2:

load *, CreateNewField resident t1;

drop tables t1;

whereby you need to use an if-loop with your conditions instead of a case-statement.

Alternative would be to load your ExtraFields at first within a mapping table and than using applymap() to access these values within your qvd-load: Mapping as an Alternative to Joining.

- Marcus

[kroberts13]

Marcus,

Thank you for the explanation. I have not used a Resident table so I am rather confused. Are you willing to put my use case in the appropriate format for my load script?  I would really appreciate it.

Thanks