I have a database with few dimensions, among them the ACCOUNT and DESCRIPTION. Among accounts I have those starting from 4xx and those starting from 5xx. the 5xx contain in their string the 4xx ones. As per attachment, there is an account no 401-101 and the 501-00214-401-101.
I would like to create a new dimension in the LOAD script (501-Group), mapping the names of 4xx account to appropriate 5xx ones (and only to 5xx ones, in case of other accounts this dimension should be blank. I did what I want to get in excel, but don't know QV formulas enough to repeat it. could you please help?
So, it's just to remember that what vlookup() does in excel, Mapping load and ApplyMap() formula does in QV.
I was successful with my task, bus would still have an additional question.
When having Mapping table in a separate excel file, everything works great. The Mapping load has its simple construction of Mapping Load ... From file.path.
However, the original database is loaded from ORACLE database, using the following construction:
here I create few additional dimensions based on the ones sitting in database...
SQL Select *
In this BasicDataLoad statement all the dimensions needed to do the mapping are loaded. question - is there a way that I use in my Mapping Load (that goes before this BasicDataLoad statement in the script) a reference to this BasecDataLoad as data source, so that I avoid keeping separate excel? I was trying the followng construction:
Mapping Load only works with two fields at a time (one which is getting mapped from and the other which is getting mapped from). There are ways to do multiple mapping from one mapping load, but the syntax get a little messy where you need to create a concatenated list of all mapped to field in the mapping load and then use ApplyMap with SubField function. I would argue creating multiple mapping tables for 3-4 different mappings because it is easy to understand. Beyond that you might want to consider doing a join. Join is an alternative to mapping. Between join and applymap, applymaps tends to be considered a little better. Don't join - use Applymap instead
With regards to mapping from Oracle, you can def. do a mapping load from any database that you may have. Again you need to only have two fields in your mapping load table.
I hope this answers all your questions. Let us know if you still have any questions.