topic Re: Join Data Without Definite Unique Identifier in QlikView

Join Data Without Definite Unique Identifier

Wed, 09 Oct 2013 08:14:06 GMT

Can you guys help me about this problem?

this is the scenario

TABLE 1				TABLE 2
DATE	NAME	ID	CODE	TRANS	DATE	CODE
2013	LOIS	2	1	55	2013	1
2013	REY	3	1	66	2013	1

how can i achieve this output?

OUTPUT
DATE	CODE	NAME	ID	TRANS
2013	1	LOIS	2	55
2013	1	REY	3	66

Re: Join Data Without Definite Unique Identifier

Gysbert_Wassenaar — Wed, 09 Oct 2013 08:25:12 GMT

Well, you can do it by creating a field with the row number in both tables. But unless you can be sure the first row of table1 should go with the first row of table2, the second row of table1 with the second of table2 etc.... you can create bogus data.

Output:

load *, rowno() as row from ...table1source...;

join load *, rowno() as row from ...table2source...;

drop field row;

Re: Join Data Without Definite Unique Identifier

flipside — Wed, 09 Oct 2013 08:27:12 GMT

Hi,

What is the logic that says TRANS 55 goes with ID 2 and TRANS 66 goes with ID 3?

flipside

Re: Join Data Without Definite Unique Identifier

Wed, 09 Oct 2013 08:52:48 GMT

my problem was It should have different TRANS..

Re: Join Data Without Definite Unique Identifier

flipside — Wed, 09 Oct 2013 08:59:14 GMT

Sorry I don't understand, can you explain further?

Re: Join Data Without Definite Unique Identifier

Wed, 09 Oct 2013 09:04:23 GMT

Inputs

TABLE 1
DATE	NAME	ID	CODE
2013	LOIS	2	1
2013	REY	3	1

TABLE 2
TRANS	DATE	CODE
55	2013	1
66	2013	1

Ideally ouput

OUTPUT
DATE	CODE	NAME	ID	TRANS
2013	1	LOIS	2	55
2013	1	REY	3	66

OUTPUT
DATE	CODE	NAME	ID	TRANS
2013	1	LOIS	2	55
2013	1	REY	3	66

but my results are

OUTPUT
DATE	CODE	NAME	ID	TRANS
2013	1	LOIS	2	66
2013	1	REY	3	66
2013	1	LOIS	2	55
2013	1	REY	3	55

Re: Join Data Without Definite Unique Identifier

flipside — Wed, 09 Oct 2013 09:13:10 GMT

At the moment your join doesn't know which row to join to, so it takes TRANS 66 and joins it to both rows, then does the same with TRANS 55. This is why you get the output in your bottom table. However you are saying you want to assign TRANS 55 to a specific row and TRANS 66 to a different specific row, there must be some logic to this. Is it, as Gysbert says, to join the tables based on the row order, or do you just want the joins to be random, or is there some other information. Without clarification the problem cannot be answered.

Re: Join Data Without Definite Unique Identifier

Wed, 09 Oct 2013 09:16:09 GMT

Unfortunately there's other column available that can be join..
Yes! How can I randomized the TRANS Column..

Re: Join Data Without Definite Unique Identifier

Gysbert_Wassenaar — Wed, 09 Oct 2013 09:31:41 GMT

If you just want a random TRANS value then you don't need table 2 at all. You can use the rand function.

TABLE1:

LOAD *, ceil(rand()*100) as TRANS INLINE [

DATE, NAME, ID, CODE

2013, LOIS, 2, 1

2013, REY, 3, 1

];

Or just use rowno() or recno() if a sequential numbering is good enough.

Re: Join Data Without Definite Unique Identifier

flipside — Wed, 09 Oct 2013 10:07:02 GMT

This might be a solution as long as you are happy with a totally random selection of the available TRANS figures (which will mean you will have the same TRANS figure selected more than once and some not at all) ...

TABLE1:

load * inline [
DATE, NAME, ID, CODE
2013, LOIS, 2, 1
2013, REY, 3, 1];

INNER join
load DATE, CODE, concat(TRANS,',') AS TRANS, count(TRANS) AS count GROUP BY DATE, CODE;
Load * inline [
TRANS, DATE, CODE
55, 2013, 1
66, 2013, 1
77, 2013, 1
88, 2013, 1];

LEFT join
load DATE, NAME, ID, CODE, subfield(TRANS,',',CEIL(rand()*count)) as randTRANS resident TABLE1;

flipside