Search for specific records in set analysis

[#1]

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!

[swuehl]

Could you explain the logic with some few sample records?

[sunny_talwar]

Would you be able to provide a few rows of sample data with your expectation for the final data?

Best,

Sunny

[#4]

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

[#5]

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

[#6]

please see RecordIDs 2567 and 2134

[swuehl]

"and find the other record with the same RecordID where  FieldB='2' and FieldC='5'"

where is this condition fulfilled in your sample?

[swuehl · Accepted solution]

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;

[#9]

Apologies, change that FieldB='2' and FieldC='5', to FieldB='0' and FieldC='2',

[#10]

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