Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
patbuchanan
New 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?

Tags (2)
1 Solution

Accepted Solutions
Highlighted
Employee
Employee

Re: How do I split one record into 2 records

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
Highlighted
MVP & Luminary
MVP & Luminary

Re: How do I split one record into 2 records

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
Highlighted
Partner
Partner

Re: How do I split one record into 2 records

You can use a crosstable load, something like

CrossTable (Type,Data,1) load

ID,

Credit,

Debet

resident Data;

Highlighted
Employee
Employee

Re: How do I split one record into 2 records

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

Highlighted
SunilChauhan
Esteemed Contributor

Re: How do I split one record into 2 records

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

Highlighted
Partner
Partner

Re: How do I split one record into 2 records

Example attached