Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
patbuchanan
Contributor III
Contributor III

How do I split one record into 2 records

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?

1 Solution

Accepted Solutions
JonnyPoole
Former Employee
Former Employee

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;

Capture.PNG

View solution in original post

5 Replies
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

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 ....


talk is cheap, supply exceeds demand
stigchel
Partner - Master
Partner - Master

You can use a crosstable load, something like

CrossTable (Type,Data,1) load

ID,

Credit,

Debet

resident Data;

JonnyPoole
Former Employee
Former Employee

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;

Capture.PNG

SunilChauhan
Champion II
Champion II

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

Sunil Chauhan
stigchel
Partner - Master
Partner - Master

Example attached