topic Applymap Max Date for Mapping Tables in QlikView

Applymap Max Date for Mapping Tables

sspawar88 — Wed, 25 Nov 2020 16:16:04 GMT

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)

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

Re: Applymap Max Date for Mapping Tables

its_anandrjs — Fri, 16 Dec 2016 07:49:25 GMT

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

Re: Applymap Max Date for Mapping Tables

Anil_Babu_Samineni — Fri, 16 Dec 2016 07:54:02 GMT

Try to append this script to your existing one

I mean this

Load * From <>;

Concatenate(B)

Load * From <>;

Left Join(B)

Load Max([Process Date]) as OnlyMaxDate

Resident B

Group By Allpossibledimensions;

Inner Join(C)

Load *

Resident C;

Re: Applymap Max Date for Mapping Tables

sspawar88 — Fri, 16 Dec 2016 09:39:11 GMT

Hi Anannd,

A table needs all dates from TIMEPQ table.

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