Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
We have two dashboards, as it picks up multiple value from the mapping table we are serious data issue in the mapping table. So the data is inconsistent between both the data models. To have a consistent data in both the data model how can we always pick the first match in while using the applymap in both the data models so that data is consistent.(or) Can we use order by while storing the data into the mapping table?
Please suggest!
Guys,
Above code will not work as the CountryCode will contains AlphaNumeric values so Min or Max wont work in this case... So was using MaxString/MinString in this case
Please suggest something if you have anything in mind
every code that will produce a mapping table
- ordered (see henric/tresesco/my answer)
- or with only one record by name and town (your answer using max/minstring group by, or with peek)
should works
hi there,
in fact mapping always chooses first element, if you want to try with another mapping script here it goes : Using firstsortedvalue
MAP_DATA:
LOAD *,Rowno() as Rowno INLINE [
Name, Town, City
N1, T1, C1
N1, T1, C2
N1, T1, C3
N2, T1, C4
N2, T1, C5
N2, T1, C6
];
CITY_MAP:
mapping load Name&'-'&Town as MAP_CITY_KEY,
City;
Load
Name,
Town,
FirstSortedValue(City,Rowno) as City
Resident MAP_DATA group by Name,Town;
DROP table MAP_DATA;
ORGINAL_TABLE:
LOAD *,
ApplyMap('CITY_MAP',Name&'-'&Town,'No Data') as City;
LOAD * INLINE [
Name, Town, Data, Bla
N1, T1, DATA1, BLA
N2, T1, DATA2, BLA BLA
N3,T1,DATA3,BLA BLA BLA
];
Thanks Massimo and Prem.
Will try your options and will update you as soon as possible
Hi Prem,
I'm trying to modify this code, by allowing it to loop through the mapping list. (see attached)
With the same key, is there any way I could get the 2nd and 3rd matches using a while/for loop?
Kindly suggest any other better methods.
Thanks in advance!