How do I split one record into 2 records

[patbuchanan]

I've gotten asked to take 1 record and make it 2 records in my load statement.  I've got an ID field and then Debit and Credit that are currently in one record and they want me to have 2 records: 1 will show ID and Debit and the other will show ID and Credit.  What's the best way to do this in the Load?

[Gysbert_Wassenaar]

Two separate load statements

Transactions:

LOAD ID, Credit as Value, 1 as Sign, 'Credit' as Type

FROM ....

CONCATENATE (Transactions)

LOAD ID, Debit as Value, -1 as Sign, 'Debit' as Type

FROM ....

[stigchel]

You can use a crosstable load, something like

CrossTable (Type,Data,1) load

ID,

Credit,

Debet

resident Data;

[JonnyPoole · Accepted solution]

Subfield() , crosstable loads and concatenate loads may all work to do this depending on the source data.

When i dreamed up a data set based on your description i did so as follows.  And then used concatentate loads to scan it twice and add records for debit and credit.

SourceData:

LOAD * INLINE [

    AccountID, Debit, Credit, month

    1, 10, 50, January

    2, 5, 15, January

    1, 15, 40, February

    2, 20, 20, February

];

NewData:

Load

  AccountID,

  'Debit' as Type,

  Debit as Amount,

  month

resident SourceData;

Concatenate (NewData)

Load

  AccountID,

  'Credit' as Type,

  Credit as Amount,

  month

resident SourceData;

drop table SourceData;

[SunilChauhan]

Load

subfield(Field,'-',1) as ID,

subfield(Field,'-',2) as Debit,

subfield(Field,'-',3) as Credit;

Load * Inline [

Field

1-100-200

2-200-300

];

hope this helps

[stigchel]

Example attached