topic Re: Merge two values in QlikView

Merge two values

Anonymous — Fri, 16 Jan 2015 19:59:23 GMT

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,

Re: Merge two values

maxgro — Fri, 16 Jan 2015 20:29:08 GMT

see attachment, 0 in match is for rows you must keep

Re: Merge two values

Anonymous — Fri, 16 Jan 2015 20:46:27 GMT

Hi Massimo,

Please share me script, expression. I have personal edition therefore I'm not able to open the qvd.

regards,

Re: Merge two values

maxgro — Fri, 16 Jan 2015 20:50:15 GMT

load *, len(field) as len inline [

field

ABC-CDE

ABC-CDEFG

XYZ-AB-001

XYZ-AB-001-C1

AAABBB

ZZZZZ

ZZZ

ZZZZ

];

load

field,

len,

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

Resident

order by field, len;

DROP Table a;

Re: Merge two values

Anonymous — Fri, 16 Jan 2015 21:12:05 GMT

Hi Massimo,

The problem is still there, as you showed in your image there is match and length column. This is not required.

Current Scenario:

ABC-CDE

ABC-CDEFG

XYZ-AB-001

XYZ-AB-001-C1

AAABBB

ZZZZZ

ZZZ

ZZZZ

and Desired output required:

ABC-CDE

XYZ-AB-001

AAABBB

ZZZ

Please help me, to solve this problem.

regards,

Re: Merge two values

maxgro — Fri, 16 Jan 2015 21:16:39 GMT

I add the match and len column to show the solution, if you don't want the 2 columns add a preceding load, in bold, to filter values with match = 0 and to just keep one column

load *, len(field) as len inline [

field

ABC-CDE

ABC-CDEFG

XYZ-AB-001

XYZ-AB-001-C1

AAABBB

ZZZZZ

ZZZ

ZZZZ

];

load field where match=0;

load

field,

len,

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

Resident

order by field, len;

DROP Table a;

Re: Merge two values

Anonymous — Fri, 16 Jan 2015 21:35:59 GMT

Dear Massimo,

No effect on the data.

This output is coming

ABC-CDE

ABC-CDEFG

XYZ-AB-001

XYZ-AB-001-C1

AAABBB

ZZZZZ

ZZZ

ZZZZ

instead of required output.

ABC-CDE

XYZ-AB-001

AAABBB

ZZZ

Re: Merge two values

maxgro — Fri, 16 Jan 2015 21:42:56 GMT

are you sure?

I reload the script and I got 4 rows

Re: Merge two values

MarcoWedel — Fri, 16 Jan 2015 23:06:54 GMT

Hi,

another solution might be:

table1:
LOAD * INLINE [
    MyField, fact
    ABC-CDE, 1
    ABC-CDEFG, 2
    XYZ-AB-001, 3
    XYZ-AB-001-C1, 4
    BCD-EFGHI-JKLM-0123, 5
    BCD-EFGHI-JKLM, 6
    BCD-EFGHI, 7
    BCD-EFGHIJKL, 8
    BCD-EFGHI-0123, 9
    CDEF-GHIJ-KLMNO, 10
    CDEF-GHIJ-KLMN, 11
    CDEF-GHI-KLMNO, 12
    DEF-GHIJ-LM, 13
    DEF-GHIJ-KL, 14
    DEF-GHIJ-LK, 15
];

Right Join
LOAD MyField Where CountMyField=1;
LOAD MyField, Count(MyField) as CountMyField Group By MyField;
LOAD * Where FieldIndex('MyField', MySubField);
LOAD MyField, Left(MyField,IterNo()) as MySubField
Resident table1 While IterNo()<=Len(MyField);

hope this helps

regards

Marco

Re: Merge two values

Anonymous — Sat, 17 Jan 2015 07:23:58 GMT

Hi Massimo,

I hope this script will give you better understanding, when I'm going to add one more field then nothing is going to be showed, please check.

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

];

load field where match=1;

load

field,

len,

value,

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

Resident

order by field, len;

DROP Table a;

Re: Merge two values

maxgro — Sat, 17 Jan 2015 07:53:42 GMT

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

load field where match=0;

Re: Merge two values

Anonymous — Sat, 17 Jan 2015 17:20:13 GMT

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,

Re: Merge two values

maxgro — Sat, 17 Jan 2015 17:38:06 GMT

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

Re: Merge two values

Anonymous — Sat, 17 Jan 2015 17:54:13 GMT

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

Re: Merge two values

maxgro — Sat, 17 Jan 2015 18:41:16 GMT

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

];

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;

load

fieldgroup as field,

sum(value) as value

Resident b

group by fieldgroup;

drop table b;

Re: Merge two values

Anonymous — Sat, 17 Jan 2015 19:18:15 GMT

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);

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;

load

fieldgroup as field,

sum(vale) as value

Resident b

group by fieldgroup;

drop table b;

Re: Merge two values

Anonymous — Sat, 17 Jan 2015 20:25:29 GMT

Hi Massimo,

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

Re: Merge two values

maxgro — Sat, 17 Jan 2015 20:33:35 GMT

Sales:

LOAD field, value, len(field) as len

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

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;

load

fieldgroup as field,

sum(value) as value

Resident b

group by fieldgroup;

drop table b;

Re: Merge two values

Anonymous — Sat, 17 Jan 2015 21:59:41 GMT

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,

Re: Merge two values

Anonymous — Sun, 18 Jan 2015 07:50:53 GMT

Hi Massimo,

Please help me to solve the issue.

Regards,