Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
i'm struggling with this problem but failed to find the correct answer , that how to get only the account with minimum date not showing the multiple account for this i have two tables
complete_account:
LOAD
CUST_SNO,
CUST_ACCT_CODE,
CONTACT_NO,
CUST_ACCT_SNO,
ENTER_DATE,
ENTER_BY,
EMAIL_INACTIVE,
UPDATE_FROM,
CRM_STATUS
FROM .....
(qvd) ;
Concatenate
LOAD
ONLINE_ACCT_CAT_INVEST_LIMIT,
PK_CUST_ACCT_SNO,
FIRST_NAME,
MARITAL_STATUS,
CUST_SNO AS PK_CUST_SNO,
SALES_AGENT,
CUST_ACCT_CODE ,
ACCT_OPEN_DATE,
ROW_ID
FROM .....(qvd);
ALL_DATA:
LOAD
FIRST_NAME,min([ACCT_OPEN_DATE]) as MIN_ACCT_OPEN_DATE
Resident complete_account group by FIRST_NAME;
Hi to keep the only the rows with min([ACCT_OPEN_DATE]) you can use an inner Join:
Table:
LOAD [fields]
from [qvd]
Inner Join (Table)
LOAD PK_CUST_ACCT_SNO, // Or the field(s) used as primary key
min([ACCT_OPEN_DATE]) as ACCT_OPEN_DATE // same field name to reduce data
resident Table;
Do i need to add group by
like this
inner join (CUSTOMER_ACCOUNT_TABLE)
Load
FIRST_NAME,
PK_CUST_SNO,
min([ACCT_OPEN_DATE]) as ACCT_OPEN_DATE
Resident CUSTOMER_ACCOUNT_TABLE group by FIRST_NAME,PK_CUST_SNO;
Yes, I forgot to add it but you need to add in a group all fields not enclosed in an aggreation function.