topic Re: How to get column from one table to another table with matched data? in QlikView

How to get column from one table to another table with matched data?

Wed, 26 Nov 2014 16:18:25 GMT

Hi,

I have two excel sheets.

Excel A have columns

code,
customer,
auditnumber,
customercommision

Excel B have columns
code,
customer,
customercommision

the values in excel A customercommision has below

4545797
462348476
383646
47566
37547
854747

values in excel A customercommision has below
4545797
462348476
45345345
383646
47566
37547
854747
363563565
565363565
4534534

How can I get auditnumber into excel B where customercommision values equals to excel A customercommision

I tried matching customercommision by using join in qvw but not getting correct result.

Please can anyone suggest me.

Thanks.

Re: How to get column from one table to another table with matched data?

rubenmarin — Wed, 26 Nov 2014 16:24:03 GMT

ExcelA:

LOAD code,
customer,
auditnumber,
customercommision

....

ExcelB:

NoConcatenate LOAD code,

customer,

customercommision

.....

Left Join Load customercommision, auditnumber resident ExcelA;

DROP Table ExcelA;

Joining only by customercommision can link some unwanted rows if differents customers have the same commision, maybe you want to use a field that really identifies the customer.

Re: How to get column from one table to another table with matched data?

alexandros17 — Wed, 26 Nov 2014 16:25:04 GMT

If only customercommision link the 2 tables then:

Load * resident Table B .....

left join

Load

auditnumber,

customercommision

resident tableA

let me know

Re: How to get column from one table to another table with matched data?

veidlburkhard — Wed, 26 Nov 2014 16:28:09 GMT

Hi Amelia,

in your script do like this:

AuditMap:

Mapping Load

customercommision,

auditnumber

From Excel A;

Load

code,

customer,

customercommision,

ApplyMap('AuditMap', customercommision, '-') as auditnumber

From Excel B;

Then you'll find the audtnumbers from A in B...

Hope this helps

Burkhard

Re: How to get column from one table to another table with matched data?

Wed, 26 Nov 2014 16:40:23 GMT

Thanks. Yes you are right joining the customercommision is not getting correct results. Please can you suggest me how to get this by joining customer.I have been struggling with this

Re: How to get column from one table to another table with matched data?

Wed, 26 Nov 2014 16:42:53 GMT

Thanks. I tried it is showing blank auditnumber. am I need to change anything in script. please suggest.

Re: How to get column from one table to another table with matched data?

veidlburkhard — Wed, 26 Nov 2014 17:59:13 GMT

Show me your script, please.

Re: How to get column from one table to another table with matched data?

rubenmarin — Thu, 27 Nov 2014 09:41:09 GMT

Hi Amelia, maybe:

ExcelA:

LOAD code,
customer,
auditnumber,
customercommision

....

ExcelB:

NoConcatenate LOAD code,

customer,

customercommision

.....

Left Join Load code, customer, auditnumber resident ExcelA;

DROP Table ExcelA;