Keep one value if duplicate exist - Sage AccPac

[sjhussain]

Hello

We are integrating data from Sage AccPac Accouting ERP and are in the process of normalizing the data.

We have two scenarios:

* Multiple Post Seq with Projects being "Not Applicable"

* Multiple Post Seq with one Project having actual entry other than "Not Applicable"

What we want to do

* Multiple Post Req with Project being "Not Applicable"

     - Remove the duplicates

     - Keep one entry and then link the Acct Desc to another Table which will have the Acct Desc and Projects

* Multiple Post Seq with one Project having actual entry other than "Not Applicable"

     - Remove the Entries with Not Applicable

     - Keep the Entries which have actual data in it.

Will appreciate assistance.

Thanks.

[jonathandienst]

First load all the data into a single temporary table:

T_Data:

LOAD [Audit Date],

  Projects,

  [Acct Desc.],

  ...

FROM <first data source(s)>;

Concatenate(T_Data)

LOAD [Audit Date],

  Projects,

  [Acct Desc.],

  ...

FROM <second data source(s)>;

Now you can set a flag (HasProject) to indicate which accounts have a valid project name.

Join (T_Data)

LOAD Max(If(Projects <> 'Not Applicable', 1, 0)) As HasProject,

  [Acct Desc.]

Resident T_Data

Group By [Acct Desc.];

And then load the data with a condition depending on the HasProject flag

Data:

NoConcatenate

LOAD DISTINCT [Audit Date],

  Projects,

  [Acct Desc.],

  ...

Resident T_Data

Where (HasProject = 0)

  Or(HasProject = 1 And Projects <> 'Not Applicable');

DROP Table T_Data;

[sjhussain]

Jonathan,

Thanks for the quick response.

Join (temp_GLPOST)

LOAD Max(If(VDESC <> 'Not Applicable', 1, 0)) As HasProject,

ACCTDESCRIPTION

Resident temp_GLPOST

Group By ACCTDESCRIPTION;

I made the changes as you suggested.

The above script is satisfying one of the criterias -

* Multiple Post Seq with one Project having actual entry other than "Not Applicable"

     - Remove the Entries with Not Applicable

     - Keep the Entries which have actual data in it.

but if all the Project Entries are "Not Applicable" then it is still showing all.  It should just keep one and remove all.

Will appreciate if you can look into it again.

Thanks.

[sasiparupudi1]

Try

T_Data:

LOAD distinct [Audit Date],

  Projects,

  [Acct Desc.],

  ...

FROM  Multiple Post Req with Project being "Not Applicable;

Concatenate(T_Data)

LOAD [Audit Date],

  Projects,

  [Acct Desc.],

  ...

FROM Multiple Post Seq with one Project having actual entry other than "Not Applicable

where (lower(trim(VDESC)) <> 'not applicable');

join(T_Data)

load * from

Account Description;

hth

Sasi