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!
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.
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;
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.
So should I not use left join in the second part?
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;
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
synthetic keys*
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.
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
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.