How do I update data while loading

[#1]

Hi All,

I have an excel document that contains:

ShowUnitName  (this is the way they want the dept to appear)

List 1 (List 1 through 4 contains the many ways some data contributors enter the units into their systems

List 2

List3

List4

ShowDivisionName (each unit is associated with a division - for example:  ICU could be located at 3 divisions)

I currently have 3 sql tables and 1 xls document loading data.  I use a concatenate to load the data.  Each time I load the data, I name the Units "LinkUnit" and I name the Divisions "LinkDivision"

I'm not sure if I should use Mapping, previous, or what...  to get the Units updated to the ShowUnitName.   My if statement would contain both the Unit and the Division to make the match.  There is no matching ID.

On the dashboard I want the dimensions to be:   Date (which I have working find), Unit and Division.  Can you please let me know how I can do this.  I'm new...

Thank you for your support!

Michelle

Message was edited by: Michelle Scullion - I've added a file. Hope it explains a little better my question. Thank you!

[miguelbraga]

Hi Michelle Scullion,

Can you please share with us a .qvw file or some samples of your data or even a document witch provide further more informations with examples?

Regards,

MB

[swuehl · Accepted solution]

As I understand, you need to do data cleansing, because data contributors are using different names for the same unit.

You could use a MAPPING approach to solve this, like this (assuming that the same names different Lists are not used for different Units):

TMPMAP:

LOAD List1 as List, ShowUnitName

FROM YourExcel;

LOAD List2 as List, ShowUnitName

FROM YourExcel;

LOAD List3 as List, ShowUnitName

FROM YourExcel;

LOAD List4 as List, ShowUnitName

FROM YourExcel;

MAP:

MAPPING

LOAD List, ShowUnitName

RESIDENT TMPMAP;

DROP TABLE TMPMAP;

FACT:

LOAD Date,

          ApplyMap('MAP', Unit, 'No Map Found') as Unit // here we clean the data entries

FROM YourFactTable;

UNIT:

LOAD ShowUnitName  as Unit,

          ShowDivisionName as Division

FROM YourExcel;

[#4]

Hi Miguel,

I uploaded sample files.  Thank you.

[#5]

Hi Swuehl,

I finally got your code to work.  I didn't realize the ApplyMap('MAP', Unit  was case sensitive.   Finally tried it again and it works!

Thank you so much for your help and support!

Have a great day.

Michelle

[#6]

Hi Swuehl,

So...   What I needed to do was for each Division, create a MAP - then utilize an If statement to for each unit, and based on the unit apply the correct map.   it took a while for me to figure it out, but it works wonderfully!

Thank you again for your support and contribution.

Sincerely,

Michelle

[swuehl]

Nice to hear that you got it working.

Another option might be to create a composite key of Division & Unit for the mapping table, then you might be able to just use a single ApplyMap('MAP', Division & Unit).