Merge two values

[Anonymous]

Hi,

I need to mege two values which are existing in the same field but in last both names are different.

For example:

MyField: These values are coming in the list box

Current Output:

ABC-CDE

ABC-CDEFG

Required Output:

Required output

ABC-CDE

Both should be merge by using great method. Such as using wildmatch etc.

Note: Some times these values are greater then 7 digit

For Example: Current scenario

XYZ-AB-001

XYZ-AB-001-C1

Requried scenario:

XYZ-AB-001

Therefore, I need a method which run in all the scenarios.

regards,

[maxgro]

in my script I have 0 not 1 because match = 1 is for values I have already found

load field where match=0;

[Anonymous]

Hi Massimo,

It's working fine when only one filed loaded, one I'm going to load one more field then it's not working properly.

I need to load one more field called Quantity, which consist on the values of field, which are merged to each other.

How to load field Quantity through your valuable script?

Regards,

[maxgro]

please post some data, or your script, I didn't understand your new requirement

[Anonymous]

Hi Massimo,

Here is my sample data, I hope It will give you clear idea.

Input:

field	value
ABC-CDE	2
ABC-CDEFG	2
XYZ-AB-001	3
XYZ-AB-001-C1	3

Required Output:

field	value
ABC-CDE	4
XYZ-AB-001	6

[maxgro]

a:

load *, len(field) as len inline [

field, value

ABC-CDE, 2

ABC-CDEFG, 2

XYZ-AB-001, 3

XYZ-AB-001-C1,3

AAABBB, 4

ZZZZZ, 5

ZZZ, 5

ZZZZ, 5

];

b:

load

  field,

  len,

  value,

  //if(wildmatch(field, peek(field) & '*') and len(peek(field))>0, 1, 0) as match,

  if(wildmatch(field, peek(field) & '*') and len(peek(field))>0, peek(fieldgroup), field) as fieldgroup

Resident a

order by field, len;

DROP Table a;

c:

load

  fieldgroup as field,

  sum(value) as value

Resident b

group by fieldgroup;

drop table b;

[Anonymous]

Hi Massimo,

Your script working fine in inline load but when I'm going to load data from outside, then it is giving issue. I'm attaching my sample sales file "Sales.xlsx" as well as script which is modified by me. Please look into and make correction where I'm going wrong.

Sales:

LOAD *,

  len(field) as len,

  value

FROM

[Sales.xlsx]

(ooxml, embedded labels, table is Sheet1);

b:

load

  field,

  len,

  value,

  if(wildmatch(field, peek(field) & '*') and len(peek(field))>0, peek(fieldgroup), field) as fieldgroup

Resident a

order by field, len;

DROP Table Sales;

c:

load

  fieldgroup as field,

  sum(vale) as value

Resident b

group by fieldgroup;

drop table b;

[Anonymous]

Hi Massimo,

Have you checked my sample file and script. please help me.

[maxgro]

Sales:

LOAD field, value, len(field) as len

FROM Sales.xlsx (ooxml, embedded labels, table is Sheet1);

b:

load

  field,

  len,

  value,

  if(wildmatch(field, peek(field) & '*') and len(peek(field))>0, peek(fieldgroup), field) as fieldgroup

Resident Sales

order by field, len;

DROP Table Sales;

c:

load

  fieldgroup as field,

  sum(value) as value

Resident b

group by fieldgroup;

drop table b;

[Anonymous]

Dear Massimo,

I'm specially thankful to you. You help me out this problem. Now It's working fine 80%. But still I need your little bit more help for achieve 100% correction.

I attached "Sales.xlsx" and "PrecdingLoadSolution.qvw". There is littlebit mismatch between field and value.

Kindly find attached screenshot and files.

Kind regards,

[Anonymous]

Hi Massimo,

Please help me to solve the issue.

Regards,