How to add Previous records value to its next record.

[vikasshana]

Hi,

I've the below example data.

ID	Desc	Eq
1	ABC	123
1	DEF	123
1	GHI	123
1	JKL	123
1	MNO	123
1	PQR	123
2		123

My requirement is to add what ever Desc's are there for ID '1' to '2', below is the expected output.

ID	Desc	Eq
1	ABC	123
1	DEF	123
1	GHI	123
1	JKL	123
1	MNO	123
1	PQR	123
2	ABC	123
2	DEF	123
2	GHI	123
2	JKL	123
2	MNO	123
2	PQR	123

common value between those two rows are Eq '123'.

I'm looking for a load script to achieve it.

Regards,

V

[martinpohl]

Hi,

use:

Facts:

load *

from your data

where len(Desc) >0;

load

'2' as ID,

Desc,

Eq

resident Facts where Eq ='123';

Regards

[fldc2500]

You could use something like this:

// Load the initial data
TempData:
LOAD * INLINE [
ID, Desc, Eq
1, ABC, 123
1, DEF, 123
1, GHI, 123
1, JKL, 123
1, MNO, 123
1, PQR, 123
2, , 123
];

// Filter data for ID = 1 and store its Desc values
ID1Data:
NOCONCATENATE
LOAD 
'2' AS ID, 
Desc, 
Eq
RESIDENT TempData
WHERE ID = 1;

// Combine the new rows with the original dataset
FinalData:
NOCONCATENATE
LOAD * RESIDENT TempData WHERE ID = 1;
CONCATENATE (FinalData)
LOAD * RESIDENT ID1Data;

// Drop intermediate tables to clean up
DROP TABLE TempData;
DROP TABLE ID1Data;

[vikasshana]

In real time I've more than one ID, where I've added only some sample concept in this question. Can we do it instead of manually adding ID '2' in ID1Data and Where ID = 1?

ID1Data:
NOCONCATENATE
LOAD 
'2' AS ID, 
Desc, 
Eq
RESIDENT TempData
WHERE ID = 1;

// Combine the new rows with the original dataset
FinalData:
NOCONCATENATE
LOAD * RESIDENT TempData WHERE ID = 1; 

[marcus_sommer]

It's not quite clear what do want to achieve but I could imagine that the following approach is more suitable to create a generic table, something like:

t: load distinct ID from X; join(t) load distinct Desc from X;

to create a cartesian product and afterwards the real existing values from X are mapped against this table.

Depending on data-set and further requirements there might be further key-fields included within the join-loads are/or the loads are filtered by any where-clauses and/or any further check/flag-logic are applied to the full-table to reduce it to real needed data-set.