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

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
vikasshana
Creator III
Creator III

How to add Previous records value to its next record.

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

4 Replies
martinpohl
Partner - Master
Partner - Master

Hi,

use:

Facts:

load *

from your data

where len(Desc) >0;

load

'2' as ID,

Desc,

Eq

resident Facts where Eq ='123';

Regards

fldc2500
Partner - Contributor III
Partner - Contributor III

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
Creator III
Creator III
Author

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.