Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Search for specific records in set analysis

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!

1 Solution

Accepted Solutions
MVP
MVP

Re: Search for specific records in set analysis

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;

21 Replies
MVP
MVP

Re: Search for specific records in set analysis

Could you explain the logic with some few sample records?

MVP
MVP

Re: Search for specific records in set analysis

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

Best,

Sunny

Not applicable

Re: Search for specific records in set analysis

rowNumRecordIDFieldAFieldBFieldCFieldNew
1121310014100
2256712012477
3348730015300
4239870013700
5256747702477
6213423402234
7267857813578
8213467812234
9126378014780
10176443015430
Not applicable

Re: Search for specific records in set analysis

rowNumRecordIDFieldAFieldBFieldCFieldNew
1121310014100
2256712012477
3348730015300
4239870013700
5256747702477
6213423402234
7267857813578
8213467812234
9126378014780
10176443015430
Not applicable

Re: Search for specific records in set analysis

please see RecordIDs 2567 and 2134

MVP
MVP

Re: Search for specific records in set analysis

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

where is this condition fulfilled in your sample?

MVP
MVP

Re: Search for specific records in set analysis

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;

Not applicable

Re: Search for specific records in set analysis

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

Not applicable

Re: Search for specific records in set analysis

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