Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have data as below in excel files
Excel A:
CID | ProductName | SaleAmount | ADNumber |
235435 | Sarfach tyu | 234 | 5563443634 |
235435 | Sarfach in US | 532 | 5449075343 |
235435 | Sarfach at the Cafeteria | 7456 | 8969596 |
534555 | Roboti | 3454 | 34590876 |
534555 | Roboti | 4545 | 34590876 |
534555 | Roboti at the caveman islands | 907 | 80978459287 |
534555 | Roboti | 890 | 66066778 |
453553 | Yuorti | 343 | 63503262384 |
453553 | Yuorti | 5364 | 930347833 |
Excel B:
CUSID | Name1 | INCount | Anumber |
235435 | Sarfach tyu | 3843432 | 5563443634/9083254-A |
235435 | Sarfach in US | 8646565 | 5449075343 |
235435 | RYIB | 1209069 | XX |
235435 | Uipo | 1209070 | XX |
235435 | Asda | 1209071 | XX |
235435 | Tesco | 1209072 | X5635653 |
235435 | Iopiu | 1209073 | 547367900 |
235435 | Lidloou | 1209074 | 63190726 |
235435 | Pouitr | 1209075 | 528689 |
534555 | Roboti | 1245234 | 34590876/9085876- |
534556 | YUIpi | 1224243 | 4453452342 |
534557 | Roboti at the caveman islands | 1233434 | 80978459287 |
534558 | Roboti | 1290765 | 99908uh/ophi890876 |
453553 | Yuorti | 8002484 | 63503262384 |
453553 | Yuorti | 8009777 | 930347833 |
453553 | Yuorti | 8006447 | 5234547653 |
453553 | Yuorti | 2414314 | 84463574 |
I need to get INCount into Excel A where CID and CUSID, ProductName and Name1, ADNumber and Anumber are matching using qvw. Would it be possible. I have tried it but when I select CID = 235435 it is showing all INCount values. But only I need is the INCount value where match exists.
Also there are values separated by '/','-', text values in Anumber eventhough if there are '/', '-' text values when ever the macth exists as 5563443634 = 5563443634/9083254-A I need INCount into Excel A. Is it possible?
Please can anyone suggest me.
Thanks.
Try something like this:
excelB_0:
Load CUSID, Name1, InCount, subfield(Anumber, if(index(Anumber, '/') > 0, '/', '-')) as Anumber From excelB;
excelB_1:
Mapping Load distinct CUSID & '|' & Name1 & '|' & Anumber as xlsKey, InCount Resident excelB_0;
excelA:
Load
CID, ProductName, SaleAmount, ADNumber,
applymap('excelB_1', CID & '|' & ProductName & '|' & ADNumber, '#NV') as InCount
From excelA;
drop tables excelb_0;
- Marcus
Thanks. It is working only for some cases not all. even though there is value in INCount but it is not populating for some cases and showing as #NV.
Please can help me do I need to change anything here.
To receive some #NV meant that not all Keys couldn't be matched. This isn't necessarily wrong and it will depend on the quality from this data how often it occured. If you are sure that a certain Key is in bothe tables available and won't be matched then have look on this keys - maybe there are further delimiter or the fields need some cleaning with trim or similar.
- Marcus
Thanks. Can you help me how to use trim? please
Trim is quite easy to use, simply wrapping the field or expression - but it could be that you have to apply trim to all fields which are you used for the key:
excelB_0:
Load CUSID, Name1, InCount, subfield(Anumber, TRIM(if(index(Anumber, '/') > 0, '/', '-'))) as Anumber
From excelB;
- Marcus
Let me know if this works for you.
Thanks. Sorry it is not working. INCount is not showing values.
There are values. The blanks are where the CID and CUSID and ProductName and Name don't match, and the ADNumber does not exist in Anumber. Should we have an INCount when only the ADNumber exists in the Anumber?