topic Re: How do I update data while loading in QlikView

How do I update data while loading

Mon, 11 Jan 2016 15:23:50 GMT

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!

Re: How do I update data while loading

miguelbraga — Mon, 11 Jan 2016 16:23:47 GMT

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,

Re: How do I update data while loading

swuehl — Mon, 11 Jan 2016 16:28:28 GMT

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;

Re: How do I update data while loading

Mon, 11 Jan 2016 20:45:30 GMT

Hi Miguel,

I uploaded sample files. Thank you.

Re: How do I update data while loading

Tue, 12 Jan 2016 16:50:38 GMT

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

Re: How do I update data while loading

Fri, 22 Jan 2016 16:03:44 GMT

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

Re: How do I update data while loading

swuehl — Fri, 22 Jan 2016 16:42:27 GMT

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).