Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I'm pretty new to Qlik. I've had a look at other threads but couldn't find the answer to what I'm trying to do so appreciate your help!
In my data, I have some records with the same RecordID. (The unique ID is rowNum field in case you're wondering)
I need to create a new FieldNew (At this point I think I need to do it in the loadscript but let me know otherwise.). The data in this FieldNew comes from below.
I need to check if FieldB='1' and FieldC='2', and if so, I need to get the RecordID (calling this "first record") and find the other record with the same RecordID where FieldB='2' and FieldC='5', and then get FieldA data for this record and put it in the FieldNew for the "first record".
Else if FieldB='1' and FieldC='2' is not correct, I'd simply put FieldA as FieldNew.
I hope I have explained it well enough. Thanks!
Maybe you can do it using something like this:
INPUT:
LOAD * INLINE [
rowNum, RecordID, FieldA, FieldB, FieldC, FieldNew
1, 1213, 100, 1, 4, 100
2, 2567, 120, 1, 2, 477
3, 3487, 300, 1, 5, 300
4, 2398, 700, 1, 3, 700
5, 2567, 477, 0, 2, 477
6, 2134, 234, 0, 2, 234
7, 2678, 578, 1, 3, 578
8, 2134, 678, 1, 2, 234
9, 1263, 780, 1, 4, 780
10, 1764, 430, 1, 5, 430
];
MAP:
MAPPING LOAD
RecordID, FieldA
Resident INPUT
WHERE FieldB= 0 and FieldC=2; // Adjust to your requirements, see my previous comment
RESULT:
NOCONCATENATE
LOAD *,
If(FieldB = 1 and FieldC =2, ApplyMap('MAP', RecordID, FieldA),FieldA) as FieldNew2
Resident INPUT;
DROP Table INPUT;
Could you explain the logic with some few sample records?
Would you be able to provide a few rows of sample data with your expectation for the final data?
Best,
Sunny
rowNum | RecordID | FieldA | FieldB | FieldC | FieldNew | ||
1 | 1213 | 100 | 1 | 4 | 100 | ||
2 | 2567 | 120 | 1 | 2 | 477 | ||
3 | 3487 | 300 | 1 | 5 | 300 | ||
4 | 2398 | 700 | 1 | 3 | 700 | ||
5 | 2567 | 477 | 0 | 2 | 477 | ||
6 | 2134 | 234 | 0 | 2 | 234 | ||
7 | 2678 | 578 | 1 | 3 | 578 | ||
8 | 2134 | 678 | 1 | 2 | 234 | ||
9 | 1263 | 780 | 1 | 4 | 780 | ||
10 | 1764 | 430 | 1 | 5 | 430 |
rowNum | RecordID | FieldA | FieldB | FieldC | FieldNew | ||
1 | 1213 | 100 | 1 | 4 | 100 | ||
2 | 2567 | 120 | 1 | 2 | 477 | ||
3 | 3487 | 300 | 1 | 5 | 300 | ||
4 | 2398 | 700 | 1 | 3 | 700 | ||
5 | 2567 | 477 | 0 | 2 | 477 | ||
6 | 2134 | 234 | 0 | 2 | 234 | ||
7 | 2678 | 578 | 1 | 3 | 578 | ||
8 | 2134 | 678 | 1 | 2 | 234 | ||
9 | 1263 | 780 | 1 | 4 | 780 | ||
10 | 1764 | 430 | 1 | 5 | 430 |
please see RecordIDs 2567 and 2134
"and find the other record with the same RecordID where FieldB='2' and FieldC='5'"
where is this condition fulfilled in your sample?
Maybe you can do it using something like this:
INPUT:
LOAD * INLINE [
rowNum, RecordID, FieldA, FieldB, FieldC, FieldNew
1, 1213, 100, 1, 4, 100
2, 2567, 120, 1, 2, 477
3, 3487, 300, 1, 5, 300
4, 2398, 700, 1, 3, 700
5, 2567, 477, 0, 2, 477
6, 2134, 234, 0, 2, 234
7, 2678, 578, 1, 3, 578
8, 2134, 678, 1, 2, 234
9, 1263, 780, 1, 4, 780
10, 1764, 430, 1, 5, 430
];
MAP:
MAPPING LOAD
RecordID, FieldA
Resident INPUT
WHERE FieldB= 0 and FieldC=2; // Adjust to your requirements, see my previous comment
RESULT:
NOCONCATENATE
LOAD *,
If(FieldB = 1 and FieldC =2, ApplyMap('MAP', RecordID, FieldA),FieldA) as FieldNew2
Resident INPUT;
DROP Table INPUT;
Apologies, change that FieldB='2' and FieldC='5', to FieldB='0' and FieldC='2',
It gives me the error below. Should I have the inline table in another script and have the rest in another? Have you tried the same code and it works? Thanks!!
The following error occurred:
Table not found
The error occurred here:
MAP: MAPPING LOAD RecordID, FieldA Resident INPUT WHERE FieldB= 0 and FieldC=2