month start status and month end status

[krishna20]

Hi Friends,

i have an requirement from client side

that is

ACCOUNT_ID	PRODUCT_ID	PRODUCT_METRICS_DIMENSION_ID	SOURCE_PRODUCT_METRICS_AID	START_DATE	END_DATE	PREV_FOR_STATUS	CURR_FOR_STATUS
397,394	1,286	1,512,649	a0E26000000qAhrEAE	12/10/2015	12/20/2015	In Progress	Rejected
397,394	1,286	1,512,649	a0E26000000qAhrEAE	12/21/2015	12/25/2015	Rejected	Approved

above the data is in put

i want out put like Below

397,394

1,286

1,512,649

a0E26000000qAhrEAE

12/10/2015

12/25/2015

In Progress

Approved

could any one have a solution Kindly reply

Thanks

[MK_QSL]

What is the logic behind this?

Do you want to do this at front end or back end?

[krishna20]

i want to do in back end

the logic behind this

one account and product combination there multiple status

but we need min months start date status and max month end status

if you want i can share my sample qvw

Thanks,

[krishna20]

Hi

find the Attached which contains sample data and qvw

Thanks

[Anonymous]

Hi,

derive the month end dates and month start date from your date filed.....

like...MonthEnd(Your Date field)

MonthStart(Your Date Filed)

then these dates in front end.

[dmitrydv]

I try to help you with your issue.

I think, the solution is to take one part of the data and then to add the other fields to the first part of the data into the results table.

tmpTable:

LOAD

ACCOUNT_ID,

PRODUCT_ID,

PRODUCT_METRICS_DIMENSION_ID,

SOURCE_PRODUCT_METRICS_AID,

START_DATE,

END_DATE,

PREV_FOR_STATUS,

CURR_FOR_STATUS

FROM

[Data for kam formularity.xlsx]

(ooxml, embedded labels, table is [limited data]);

tmp1:

LOAD

ACCOUNT_ID,

PRODUCT_ID,

PRODUCT_METRICS_DIMENSION_ID,

SOURCE_PRODUCT_METRICS_AID,

Min(START_DATE) as START_DATE

Resident tmpTable

GROUP BY

ACCOUNT_ID,

PRODUCT_ID,

PRODUCT_METRICS_DIMENSION_ID,

SOURCE_PRODUCT_METRICS_AID;

LEFT JOIN (tmp1)

LOAD

ACCOUNT_ID,

PRODUCT_ID,

PRODUCT_METRICS_DIMENSION_ID,

SOURCE_PRODUCT_METRICS_AID,

START_DATE,

PREV_FOR_STATUS

RESIDENT tmpTable;

tmp2:

LOAD

ACCOUNT_ID,

PRODUCT_ID,

PRODUCT_METRICS_DIMENSION_ID,

SOURCE_PRODUCT_METRICS_AID,

Max(END_DATE) as END_DATE

Resident tmpTable

GROUP BY

ACCOUNT_ID,

PRODUCT_ID,

PRODUCT_METRICS_DIMENSION_ID,

SOURCE_PRODUCT_METRICS_AID;

LEFT JOIN (tmp2)

LOAD

ACCOUNT_ID,

PRODUCT_ID,

PRODUCT_METRICS_DIMENSION_ID,

SOURCE_PRODUCT_METRICS_AID,

END_DATE,

CURR_FOR_STATUS

RESIDENT tmpTable;

LEFT JOIN (tmp1)

LOAD * RESIDENT tmp2;

DROP Tables tmpTable, tmp2;

RENAME Table tmp1 to Result;