Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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!

21 Replies
swuehl
MVP
MVP

No, that's one load script.

Replace the INPUT table LOAD with the LOAD statement for your table. The mapping LOAD and RESULT table LOAD statements should then reference this resident table.

Not applicable
Author

Great thanks!

So now I am having difficulties joining this with my main table. I imagine we're calling my main table "INPUT". Should below work? (i have just changed the loading part for the second portion.)

MAP:

MAPPING LOAD

RecordID, FieldA

Resident INPUT

WHERE FieldB= 0 and FieldC=2; // Adjust to your requirements, see my previous comment

Left Join(INPUT)

LOAD *,

  If(FieldB = 1 and FieldC =2, ApplyMap('MAP', RecordID, FieldA),FieldA) as FieldNew2

Resident INPUT;

swuehl
MVP
MVP

You need to label your main table 'INPUT':

INPUT:

LOAD Field1,

          Field2,

....

Or use any other label and reference this label in the resident LOAD statements.

Not applicable
Author

So should I not use left join in the second part?

Not applicable
Author

Imagine INPUT is my main table. Is below correct? (I'm having another load script beside my main data loading)

MAP:

MAPPING LOAD

RecordID, FieldA

Resident INPUT

WHERE FieldB= 0 and FieldC=2; // Adjust to your requirements, see my previous comment

Left Join(INPUT)

LOAD *,

  If(FieldB = 1 and FieldC =2, ApplyMap('MAP', RecordID, FieldA),FieldA) as FieldNew2

Resident INPUT;

Not applicable
Author

When I don't do left join it gives synthetic things. And when I do (as above), it gives me all nulls for the new field...not sure what the problem is

Not applicable
Author

synthetic keys*

swuehl
MVP
MVP

Try with my suggested approach using a RESIDENT LOAD with no JOIN prefix and a following

DROP TABLE INPUT;

to drop the original table.

This should eliminate the synthetic keys.

Not sure why you currently see only NULL in the new field, but I would first try to create the field in a RESIDENT LOAD.

Not applicable
Author

Thanks for your prompt reply.

You mean just have the code you have right? I don't want to drop my main (INPUT) table since I'm using it everywhere. Not sure what I should do

swuehl
MVP
MVP

The new table RESULT will have all fields and records, identical to your original MAIN table, it will just have an additional new field...

It would be helpful if you could post your current script.