Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I've loaded an 875x24 table using multiple noconcatenate loads and joins, however when I attempt to do analysis on the table, it gives the wrong number of rows and the analysis is skewed.
Do you know why this may be happening and how to remove these ghost rows?
Thank you!
Charlie
Edit: Final solution
Instead of using a join to merge tables in which the field they share contains multiple of the same value, I ultimately used applymap to 'vlookup' the values and add them to the final table. Thank you for your help!
Map1:
Mapping LOAD
[Task Match],
MaxCDate
Resident TableD;
Map2:
Mapping LOAD
[Task Match],
MaxDue
Resident TableD;
Map3:
Mapping LOAD
[Task Match],
ECCheck
Resident TableD;
FinalMapped:
LOAD *,
ApplyMap('Map1',[Task Match],Null()) as MaxCDate,
ApplyMap('Map2',[Task Match],Null()) as MaxDue,
ApplyMap('Map3',[Task Match],Null()) as ECCheck
Resident NewTable;
DROP Tables TableD, NewTable;
EXIT Script
You have multiple rows with the same [Task Match], so the join will create additional rows. If all the other field values are identical for each [Task Match], then you should be able ot get away with making TableD distinct. Add the DISTINCT keyword to the creation of TableD.
-Rob
Your join is having issues. cross check their relationships.
I agree with Rob and Karthikeyan! There are high chances that the join strategy you used in not correct. Request you to debug the script code by breaking into smaller parts/commenting & un-commenting sub parts. Hope this makes sense.
Regards!
Rahul
Instead of using a join to merge tables in which the field they share contains multiple of the same value, I ultimately used applymap to 'vlookup' the values and add them to the final table. Thank you for your help!
Map1:
Mapping LOAD
[Task Match],
MaxCDate
Resident TableD;
Map2:
Mapping LOAD
[Task Match],
MaxDue
Resident TableD;
Map3:
Mapping LOAD
[Task Match],
ECCheck
Resident TableD;
FinalMapped:
LOAD *,
ApplyMap('Map1',[Task Match],Null()) as MaxCDate,
ApplyMap('Map2',[Task Match],Null()) as MaxDue,
ApplyMap('Map3',[Task Match],Null()) as ECCheck
Resident NewTable;
DROP Tables TableD, NewTable;
EXIT Script