Applymap Max Date for Mapping Tables

[sspawar88]

Hi Community,

I'm trying to solve following problem where through one mapping Table TIMEPQ we have taken dates into A, B, C table

In A table

1) we are fetching all dates from TIMEPQ through applymap. thats ok

In B & C

1) We want data only for Max of Process date throughout all fileds in both tables.

2) Between B&C there is Concatation

TIMEPQ:

MAPPING LOAD
DQ_TIME_ID,
Date(BUSINESS_DATE,'MM/DD/YYYY')as BUSINESS_DATE
FROM C:\Users\TIMEPQ.qvd(qvd);

A:
LOAD
DQ_TIME_ID AS DQ_TIME_ID1,
ApplyMap('TIMEPQ',DQ_TIME_ID,Null()) as LOAD_DATE,
CHECK_POINT_ID,
ROW_CNT
FROM C:\Users\A.qvd(qvd);

B:
LOAD 
CHECK_POINT_ID,
DQ_TIME_ID,
RowNo() as rowno,
RecNo() as recno,
ApplyMap('TIMEPQ',DQ_TIME_ID) as [Process Date],
EXCEPTION_DETAIL_1,
EXCEPTION_DETAIL_2
FROM C:\Users\B.qvd(qvd); 

Concatenate(B)

C:

LOAD 
CHECK_POINT_ID, 
DQ_TIME_ID,
ApplyMap('TIMEPQ',DQ_TIME_ID) as [Process Date],
EXCEPTION_QTY,
TOTAL_ROW_QTY

FROM C:\Users\C.qvd(qvd);

[its_anandrjs]

Dear Satish,

Try this ways for table B and C take new mapping table with only max date as you required and map this table dates with B and C.

TIMEPQ:
MAPPING LOAD
DQ_TIME_ID,
Date(BUSINESS_DATE,'MM/DD/YYYY') as BUSINESS_DATE
FROM C:\Users\TIMEPQ.qvd(qvd);

//Max Date Table
MAXTIMEPQ:
MAPPING LOAD
DQ_TIME_ID,
Date( Max(BUSINESS_DATE) ,'MM/DD/YYYY') as BUSINESS_DATE
FROM C:\Users\TIMEPQ.qvd(qvd)
Group By DQ_TIME_ID;

A:
LOAD
DQ_TIME_ID AS DQ_TIME_ID1,
ApplyMap('TIMEPQ',DQ_TIME_ID,Null()) as LOAD_DATE,
CHECK_POINT_ID,
ROW_CNT
FROM C:\Users\A.qvd(qvd);


B:
LOAD
CHECK_POINT_ID,
DQ_TIME_ID,
RowNo() as rowno,
RecNo() as recno,
ApplyMap('MAXTIMEPQ',DQ_TIME_ID) as [Process Date],
EXCEPTION_DETAIL_1,
EXCEPTION_DETAIL_2
FROM C:\Users\B.qvd(qvd);

Concatenate(B)
C:
LOAD
CHECK_POINT_ID,
DQ_TIME_ID,
ApplyMap('MAXTIMEPQ',DQ_TIME_ID) as [Process Date],
EXCEPTION_QTY,
TOTAL_ROW_QTY
FROM C:\Users\C.qvd(qvd);

Regards,

Anand

[Anil_Babu_Samineni]

Try to append this script to your existing one

I mean this

A:

Load * From <>;

B:

Load * From <>;

Concatenate(B)

C:

Load * From <>;

Left Join(B)

Load Max([Process Date]) as OnlyMaxDate

Resident B

Group By Allpossibledimensions;

Inner Join(C)

Load *

Resident C;

[sspawar88]

Hi Anannd,

A table needs all dates from TIMEPQ table.

If we Just fetch Max dates it will fetch only MAX Date