Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
hopefully this is a simple question for the community.
I have a table that has the following three fields and sample data as below.
%POSItemId | POSGLEffectiveDateTEMP | %GLAccount |
---|---|---|
A | 02.01.2009 | 999 |
A | 02.01.2009 | 666 |
A | 02.01.2008 | 777 |
A | 02.01.2008 | 555 |
B | 02.01.2009 | 333 |
B | 02.01.2008 | 222 |
Would like it to look like this after the load:
%POSItemId | POSGLEffectiveDateTEMP | %GLAccount |
---|---|---|
A | 02.01.2009 | 999 |
A | 02.01.2009 | 666 |
B | 02.01.2009 | 333 |
To reduce the table size on load I'm trying to only load in the maximum date for each %POSItemId. However, there can be multiple GLAccounts associated with each %POSItemId.
I've been trying to do this, see below, but hasn't worked.
D_POSItemPLUGL:
LOAD POS_ITEM_ID as %POSItemId
,GL_ACCOUNT_ID as %GLAccountId
,Date(Floor(EFFECTIVE_DATE))
as POSGLEffectiveDateTEMP
FROM [$(vG.QVDPath)Class\Extract\CLASS.EDLPOSITEMPLUGL.qvd] (qvd);
Left Join (D_POSItemPLUGL)
LOAD GL_ACCOUNT_ID as %GLAccountId
,GL_ACCOUNT_NUMBER as %GLAccountNumber
FROM [$(vG.QVDPath)Class\Extract\CLASS.EDLGLACCOUNT.qvd] (qvd);
Drop Field %GLAccountId;
Left Join (POSLinkTable)
LOAD * Resident D_POSItemPLUGL
Where %POSItemId <> Previous(%POSItemId)
Order by %POSItemId, POSGLEffectiveDateTEMP DESC;
Drop Table D_POSItemPLUGL;
Any advice would be greatly appreciated.
Try this:
D_POSItemPLUGL:
LOAD
POS_ITEM_ID as %POSItemId
,GL_ACCOUNT_ID as %GLAccountId
,Date(Floor(EFFECTIVE_DATE)) as POSGLEffectiveDateTEMP
FROM [$(vG.QVDPath)Class\Extract\CLASS.EDLPOSITEMPLUGL.qvd] (qvd);
Right Join
LOAD
%POSItemId,
max(POSGLEffectiveDateTEMP) as POSGLEffectiveDateTEMP
Resident D_POSItemPLUGL
Group By %POSItemId;
Try this:
D_POSItemPLUGL:
LOAD
POS_ITEM_ID as %POSItemId
,GL_ACCOUNT_ID as %GLAccountId
,Date(Floor(EFFECTIVE_DATE)) as POSGLEffectiveDateTEMP
FROM [$(vG.QVDPath)Class\Extract\CLASS.EDLPOSITEMPLUGL.qvd] (qvd);
Right Join
LOAD
%POSItemId,
max(POSGLEffectiveDateTEMP) as POSGLEffectiveDateTEMP
Resident D_POSItemPLUGL
Group By %POSItemId;
Worked like a charm. Thank you for the clear and fast response, very helpful.
Jon