How to copy column from one table to another table using qvw?

[#1]

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.

[marcus_sommer]

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

[#3]

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.

[marcus_sommer]

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

[#5]

Thanks. Can you help me how to use trim?  please

[marcus_sommer]

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

[edi]

Let me know if this works for you.

[#8]

Thanks. Sorry it is not working. INCount is not showing values.

[edi]

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?