Merged table ???
I have need to produce a merged table from a table and a spreadsheet
The table that contains all the matches for a package – “ad_group_to_package”
It has a large number of other fields as you can see from the example – spreadsheet attached - “Decisionmapping _logic”
All fields are nvarchar
We now have a spreadsheet that has been worked on manually – that shows many packages by
Packagename, AD group & the Actual target package that needs to be matched against the ‘packagename’
This is cleaned and more accurate match to the packages found.
The target_package column is definitive as its been manually decided its the best match.
The spreadsheet needs to be matched against the table to find all the packages that match and mark them to use the defined ‘target package’ and not the other versions that maybe in the table under the same group.
The ad group has many occurrences of the package name with all the errors you get with a direct data import.
We need to use the spreadsheet to create a new table that has only the matches between the spreadsheet and table “ad group to package”
I need a complete solution please
The table ad group to package is small as is the spreadsheet,”decisionmappi
Im open to how it should be done -
I can bulk insert the spreadsheet as tab delimited file into to a table if that helps the merging and joins – so we can assume Decisionmapping_logic could be a raw table.
Final result :
We would be left with a merged table that included all the records from “Ad group to package” table – but where there were multiple matches for a packagename all point to one correct ‘Target package’
The merged table should have a field that recorded the correct TargetPackage – from decisionMapping_logic
Also if possible it would be useful to have a new flag column introduced that could be marked as true when there are many packages and they have been matched to 1 ‘target package’ in new table.
This column could be called ‘targetPackage_flag’
I need a complete solution please with all sql working and not just suggestions.
SQL 2005 server version
Thank you very much
Mo
DecsionMapping-logic.xlsx
Ad-group-to-package.xlsx
The table that contains all the matches for a package – “ad_group_to_package”
It has a large number of other fields as you can see from the example – spreadsheet attached - “Decisionmapping _logic”
All fields are nvarchar
We now have a spreadsheet that has been worked on manually – that shows many packages by
Packagename, AD group & the Actual target package that needs to be matched against the ‘packagename’
This is cleaned and more accurate match to the packages found.
The target_package column is definitive as its been manually decided its the best match.
The spreadsheet needs to be matched against the table to find all the packages that match and mark them to use the defined ‘target package’ and not the other versions that maybe in the table under the same group.
The ad group has many occurrences of the package name with all the errors you get with a direct data import.
We need to use the spreadsheet to create a new table that has only the matches between the spreadsheet and table “ad group to package”
I need a complete solution please
The table ad group to package is small as is the spreadsheet,”decisionmappi
Im open to how it should be done -
I can bulk insert the spreadsheet as tab delimited file into to a table if that helps the merging and joins – so we can assume Decisionmapping_logic could be a raw table.
Final result :
We would be left with a merged table that included all the records from “Ad group to package” table – but where there were multiple matches for a packagename all point to one correct ‘Target package’
The merged table should have a field that recorded the correct TargetPackage – from decisionMapping_logic
Also if possible it would be useful to have a new flag column introduced that could be marked as true when there are many packages and they have been matched to 1 ‘target package’ in new table.
This column could be called ‘targetPackage_flag’
I need a complete solution please with all sql working and not just suggestions.
SQL 2005 server version
Thank you very much
Mo
DecsionMapping-logic.xlsx
Ad-group-to-package.xlsx
Yep, I will look at it...
But not sure if I understand the real problem...
In the manually prepared spreadsheet DecisionMapping-logic.xlsx
So, that means that the packagename in the table ad-group-to-package will reflect the new target where there is a match on packagename between the two data sources.
So, in the case of DecisionMapping-Logic we see that
"1desksvc-2-0-custom-setti
Is that correct ? Is that everything that we need to do ?
Are there primary keys and possible duplicates as a result ?
But not sure if I understand the real problem...
In the manually prepared spreadsheet DecisionMapping-logic.xlsx
So, that means that the packagename in the table ad-group-to-package will reflect the new target where there is a match on packagename between the two data sources.
So, in the case of DecisionMapping-Logic we see that
"1desksvc-2-0-custom-setti
Is that correct ? Is that everything that we need to do ?
Are there primary keys and possible duplicates as a result ?
Oh, and then what do we do with entries like "1desksvc-2-0-custom-setti
And then similar for "1DESKSVC-2-0-CUSTOM-SETTI
Or do we simply ignore them because there is no entry in the table ?
And then similar for "1DESKSVC-2-0-CUSTOM-SETTI
Or do we simply ignore them because there is no entry in the table ?
ASKER
Oh, and then what do we do with entries like "1desksvc-2-0-custom-setti
And then similar for "1DESKSVC-2-0-CUSTOM-SETTI
Or do we simply ignore them because there is no entry in the table ?
Answer:
t1DESKSVC-2-0-CUSTOM-SETTI
Because there is valid entry in Target package column [duplicated in this column] - then this should be a roll up correct entry for all packages that match in 'Packagename' column.
1desksvc-2-0-custom-settin
I tried to give an example of the good and bad matches found in "DecsionMapping_logic"
A good match being '1desksvc-2-0-custom-setti
which shows that where packagename= many and ADGroup field is many - they should all be mapped to Target Package value
And then similar for "1DESKSVC-2-0-CUSTOM-SETTI
Or do we simply ignore them because there is no entry in the table ?
Answer:
t1DESKSVC-2-0-CUSTOM-SETTI
Because there is valid entry in Target package column [duplicated in this column] - then this should be a roll up correct entry for all packages that match in 'Packagename' column.
1desksvc-2-0-custom-settin
I tried to give an example of the good and bad matches found in "DecsionMapping_logic"
A good match being '1desksvc-2-0-custom-setti
which shows that where packagename= many and ADGroup field is many - they should all be mapped to Target Package value
ASKER
Yep, I will look at it...
But not sure if I understand the real problem...
In the manually prepared spreadsheet DecisionMapping-logic.xlsx
So, that means that the packagename in the table ad-group-to-package will reflect the new target where there is a match on packagename between the two data sources.
So, in the case of DecisionMapping-Logic we see that
"1desksvc-2-0-custom-setti
Is that correct ? Is that everything that we need to do ?
Are there primary keys and possible duplicates as a result ?
In Answer - correct about the replace
No primary keys and many duplicates - hence the decisionmapping spreadsheet to try and mark those that are relevant and exclude others
But not sure if I understand the real problem...
In the manually prepared spreadsheet DecisionMapping-logic.xlsx
So, that means that the packagename in the table ad-group-to-package will reflect the new target where there is a match on packagename between the two data sources.
So, in the case of DecisionMapping-Logic we see that
"1desksvc-2-0-custom-setti
Is that correct ? Is that everything that we need to do ?
Are there primary keys and possible duplicates as a result ?
In Answer - correct about the replace
No primary keys and many duplicates - hence the decisionmapping spreadsheet to try and mark those that are relevant and exclude others
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
this looks good Mark
good explanation
I havent had a chance to try it today however
and will do so tomorrow
if you have time have a look at new question too
same database but not same question
https://www.experts-exchange.com/questions/27439337/URGENT-Count-not-working-as-expected-in-table-SQL-2005-URGENT.html
good explanation
I havent had a chance to try it today however
and will do so tomorrow
if you have time have a look at new question too
same database but not same question
https://www.experts-exchange.com/questions/27439337/URGENT-Count-not-working-as-expected-in-table-SQL-2005-URGENT.html
Yep, can do...
Just a note on the above, I forgot to update the flag ‘targetPackage_flag’
So, on the "UPDATE a ..." line simply add in the next column to be updated e.g.
That assumes that the column has been added to the table already.
Just a note on the above, I forgot to update the flag ‘targetPackage_flag’
So, on the "UPDATE a ..." line simply add in the next column to be updated e.g.
update a set a.packagename = d.[target package], targetPackage_flag = 'Y'That assumes that the column has been added to the table already.
ASKER
Thanks Mark
This seems to have worked under this scope
Which with this project may change
Thanks for good explanation and well
Presented less for me to do
Please keep an eye on this thread as I'll try and update it if possible with
New links to new questions
Which their will be.
Hopefully an update here will alert you too
This seems to have worked under this scope
Which with this project may change
Thanks for good explanation and well
Presented less for me to do
Please keep an eye on this thread as I'll try and update it if possible with
New links to new questions
Which their will be.
Hopefully an update here will alert you too
ASKER
Excellent comments and easy to read and try
ASKER
another question that is related to this question
https://www.experts-exchange.com/questions/27446119/Urgent-Having-count-with-a-calculation-in-select.html
https://www.experts-exchange.com/questions/27446119/Urgent-Having-count-with-a-calculation-in-select.html
ASKER