topic Re: Mapping in QlikView

Mapping

Tue, 11 Dec 2012 06:34:47 GMT

Hello there !

I have 2 tables, one with my visit logs on my site and structured as:

Table 1:

Visit date | visitor ID | Order ID | Visit source | Visit number

One with my sales data as:

Table 2:

Order ID | Customer ID | Sales amounts

I haven't been able to properly link Customer ID (table 2) and visitor ID (table 1) to be able to see:

Customer ID | visitor ID | Visit source | Visit number | Sales amount

Can anybody help me with that, I've spent hours on this. There might be something to be done with applymap but haven't been successful yet...

Thanks in advance !

Re: Mapping

Tue, 11 Dec 2012 06:41:49 GMT

Is Customer ID and Visiter ID Same?

Re: Mapping

Tue, 11 Dec 2012 06:43:44 GMT

Hi,

join two tables with Order ID column, it works. if you have still problem then post sample qvw file,so that we can help you.

Niranjan.

Re: Mapping

Tue, 11 Dec 2012 06:45:52 GMT

No unfortunately. Visitor ID is the ID allocated to users on my site.

Customer ID is the ID allocated to users in my sales system.

One customer ID will probably have several visitor IDs...

Thank you !!

Re: Mapping

Tue, 11 Dec 2012 06:51:52 GMT

I've already tried a join and a left join with no success. What kind of

join do you think about ?

Right now all my file has is:

LOAD Date,

Visitor_ID,

FROM

(txt, utf8, embedded labels, delimiter is ',', msq);

LOAD ,

Customer_ID,

Sales

FROM

(txt, codepage is 932, embedded labels, delimiter is ',', msq);

Re: Mapping

sujeetsingh — Tue, 11 Dec 2012 06:52:36 GMT

Just Load Table1....in a Temp table

Table1:

load

Visit date

visitor ID

Order ID

Visit source

Visit number

sql...;

Table2:

load

Order ID

Customer ID

Sales amounts

sql..... ;

Finaly qlikview will join the both tables itself.....by Synthetic key

Else you want to do it at more advanced level...you can proceed as follow

Table_Main:

outter join (Table1)

Order ID

Customer ID

Sales amounts

resident Table2;

drop Table2;

drop Table1;

Hope it will help you....

Re: Mapping

Tue, 11 Dec 2012 06:54:15 GMT

Hi,

u can create key like this,

Table1:

Load

Visit date , visitor ID , Order ID as Key_Order, Visit source , Visit number

from 1sttable;

Table2:

Load

Order ID as Key_Order, Customer ID , Sales amounts

from 2ndTable;

or you can join the table like below

Table1:

Load

visitor ID , Order ID, Visit source , Visit number

from 1sttable;

join

Table2:

Load

Order ID, Customer ID , Sales amounts

regards,

Kabilan K.

Re: Mapping

Tue, 11 Dec 2012 06:57:16 GMT

Thanks ! Already tried the 1st method but it only allocated the visitor ID

correctly when there is a purchase (and thus a link on Purchase ID). I need

it to link visitor ID and Customer ID even when there's no Purchase ID...

Re: Mapping

Tue, 11 Dec 2012 07:00:16 GMT

I tried adding the join but the result is the same:

It only allocated the visitor ID correctly when there is a purchase (and

thus a link on Purchase ID). I need it to link visitor ID and Customer ID

even when there's no Purchase ID... (sorry I'm copy/pasting the reply I

already made above).

Thanks for helping !

Re: Mapping

sujeetsingh — Tue, 11 Dec 2012 07:00:23 GMT

Just rename customerID as Visitor id.

And then place joins.

Re: Mapping

sujeetsingh — Tue, 11 Dec 2012 07:01:49 GMT

Why using Mapping....

Just forget mapping here

Re: Mapping

Tue, 11 Dec 2012 07:02:04 GMT

They aren't the same...

Re: Mapping

Tue, 11 Dec 2012 07:03:05 GMT

Right now, this is what happens, the link only happens when the visit lead to a Purchase:

Re: Mapping

sujeetsingh — Tue, 11 Dec 2012 07:03:09 GMT

Try joining with only Visitor iD

and rename OrderID too to a diffrent allias name

Re: Mapping

sujeetsingh — Tue, 11 Dec 2012 07:07:16 GMT

Place a sample and explain the linking issues in dashboard with textbox,,,

Re: Mapping

Thu, 13 Dec 2012 05:55:49 GMT

Alright, I found a solution:

First load the sales table

Then load only visitor ID and Order ID from table 2

Then load only visitor ID and the rest of the visit data from table 2

I do "normal" loads (no code added) but my computer seems to have

difficulties coping. Is there a better load method, CPU-wise ?

Re: Mapping

er_mohit — Thu, 13 Dec 2012 08:01:26 GMT

is your visitor id and customer id same...???

because in mapping the first condition is to add the common field and after that which fields you want i.e sales amount according to your request

if you will tried to write visitor id as customer id .then it works

Re: Mapping

Thu, 13 Dec 2012 08:03:53 GMT

No, they're not. My only common field is order ID

Re: Mapping

sujeetsingh — Thu, 13 Dec 2012 08:32:12 GMT

Actually it seeems i am not able to catch your need.

What about using orderid to join both table.

even try to use concatenate..

Plz give sample .

Re: Mapping

er_mohit — Thu, 13 Dec 2012 09:17:50 GMT

istly u have to load table 1.in that you just write visitor id as customer id

and after that load table 2 in which you just using mapping load and write fields which you want i.e

customer id and then sales amount.

to do this, visitor id combined with customer id.

hope this will help you.