topic Re: How do I split one record into 2 records in QlikView

How do I split one record into 2 records

patbuchanan — Mon, 13 Apr 2015 15:48:06 GMT

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?

Re: How do I split one record into 2 records

Gysbert_Wassenaar — Mon, 13 Apr 2015 15:56:56 GMT

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

Re: How do I split one record into 2 records

stigchel — Mon, 13 Apr 2015 15:58:59 GMT

You can use a crosstable load, something like

CrossTable (Type,Data,1) load

ID,

Credit,

Debet

resident Data;

Re: How do I split one record into 2 records

JonnyPoole — Mon, 13 Apr 2015 16:01:05 GMT

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;

Re: How do I split one record into 2 records

SunilChauhan — Mon, 13 Apr 2015 16:21:48 GMT

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

Re: How do I split one record into 2 records

stigchel — Mon, 13 Apr 2015 16:21:57 GMT

Example attached