topic Re: If Statement QVD in App Development

If Statement QVD

kroberts13 — Thu, 31 Aug 2017 12:12:27 GMT

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

Re: If Statement QVD

ankur_abhishek — Thu, 31 Aug 2017 12:22:24 GMT

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

Re: If Statement QVD

kroberts13 — Thu, 31 Aug 2017 15:49:39 GMT

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);

Re: If Statement QVD

kroberts13 — Wed, 06 Sep 2017 17:05:41 GMT

Any thoughts on this? I really appreciate it

Re: If Statement QVD

marcus_sommer — Thu, 07 Sep 2017 10:26:06 GMT

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

Re: If Statement QVD

kroberts13 — Tue, 12 Sep 2017 18:15:16 GMT

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