Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Mapping

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 !

32 Replies
Not applicable

Re: Mapping

Hi

Is Customer ID and Visiter ID Same?

Not applicable

Re: Mapping

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.

Not applicable

Re: Mapping

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 !!

Not applicable

Re: Mapping

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

sujeetsingh
Honored Contributor III

Re: Mapping

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....





Not applicable

Re: Mapping

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.

Not applicable

Re: Mapping

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...

Not applicable

Re: Mapping

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 !

sujeetsingh
Honored Contributor III

Re: Mapping

Just rename customerID as Visitor id.

And then place joins.