-
1. Re: Join two variables (sheet 1) to a single variable (sheet 2)
Tharashasank Davuluru Feb 13, 2017 3:10 AM (in response to Ivar Arnarsson)2 of 2 people found this helpfulHi Ivar,
Try to create a custom sql instead of writing a calculated filed. Join them on the condition you mentioned. it will help to resolve the issue.
Thanks,
Tharashasank
-
2. Re: Join two variables (sheet 1) to a single variable (sheet 2)
Pritesh Foujdar Feb 13, 2017 3:35 AM (in response to Tharashasank Davuluru)Hi Ivar,
Hope this will solve your problem.
Thanks,
Pritesh
-
Sales and Dept.twbx 17.7 KB
-
-
3. Re: Join two variables (sheet 1) to a single variable (sheet 2)
Ivar Arnarsson Feb 13, 2017 3:55 AM (in response to Tharashasank Davuluru)Hi Tharashasank,
Thanks for the reply. I'm going to try that solution, i could have never guessed it so thanks for the guidance. Never done a custom sql before, but i'll look it up and tryThanks, Ívar
-
4. Re: Join two variables (sheet 1) to a single variable (sheet 2)
Anna Cheetham Feb 13, 2017 3:55 AM (in response to Ivar Arnarsson)1 of 1 people found this helpfulHi Ivar
If the idea of writing the custom sql that Tharashasank suggests doesn't appeal for any reason then I think I have an alternative solution.
I may be missing something that makes this not work but could you create a calculated field in the "SalesCars" data that converts all old department codes to new department codes, keeping the new department code where that's what you have.
So, for example:
CASE [Department Code]
WHEN '2300' THEN 'AB0002'
WHEN 'XT001' THEN 'AB0004'
WHEN '25863' THEN 'AB0005'
WHEN 'XB00R' THEN 'AB0006'
WHEN 'XB99C' THEN 'AB0007'
WHEN '10100' THEN 'AB0009'
WHEN '10110' THEN 'AB0010'
WHEN '10102' THEN 'AB0011'
WHEN '10103' THEN 'AB0012'
WHEN 'XC003' THEN 'AB0014'
WHEN 'XC004' THEN 'AB0015'
WHEN 'XXX001' THEN 'AB0018'
ELSE [Department Code]
END
Then you can just link this calculated field to the new department code field in the HierarchyCars data set.
I'm assuming here that the list of old department codes is now fixed and there won't be any new old department codes (if that makes sense) as you get new and refreshed data. If there will then maintaining the code might make this solution impractical. Also, if the list of old department codes is extremely long then writing the formula for the calculated field might be too tedious to want to attempt.
Hope this helps
Best wishes
Anna
AB0001 2300 AB0002 AB0003 XT001 AB0004 25863 AB0005 XB00R AB0006 XB99C AB0007 AB0008 10100 AB0009 10110 AB0010 10102 AB0011 10103 AB0012 AB0013 XC003 AB0014 XC004 AB0015 AB0016 AB0017 XXX001 AB0018 -
5. Re: Join two variables (sheet 1) to a single variable (sheet 2)
Ivar Arnarsson Feb 14, 2017 1:07 AM (in response to Tharashasank Davuluru)Hi Tharashasank,
I should i make "old department code" and "new department code" equal within the "HierarchyCars" sheet? (not sure how to make them equal).
Or do you mean to LEFT join SalesCars$Department_code to HierarchyCars$Old_department_code and then again for HierarchyCars$New_department_code?
Thanks, Ívar
-
6. Re: Join two variables (sheet 1) to a single variable (sheet 2)
Tharashasank Davuluru Feb 14, 2017 2:55 AM (in response to Ivar Arnarsson)2 of 2 people found this helpfulHi Ivar,
Yes I mean to LEFT join SalesCars$Department_code to HierarchyCars$Old_department_code and then again for HierarchyCars$New_department_code.
This will get you the desired data.
Thanks,
Tharashasank
-
7. Re: Join two variables (sheet 1) to a single variable (sheet 2)
Ivar Arnarsson Feb 14, 2017 5:24 AM (in response to Anna Cheetham)Hi Anna,
Your suggested solution works perfectly on my example data. Thanks! I got one long nice list that i wanted.
But i was over 1000 apartment codes that need to be affected by this and hand typing them is though
Regards, Ívar