topic Re: How to join two tables in QlikView

How to join two tables

Wed, 12 Feb 2014 04:46:57 GMT

Hi All,

I have two flat file like,

I want only a mismatch value.How to get it.

Re: How to join two tables

sujeetsingh — Wed, 12 Feb 2014 05:01:16 GMT

See this sample

Re: How to join two tables

Wed, 12 Feb 2014 05:11:53 GMT

Hi sujeetsingh,

i can't able to load this file.Please give Query.

Re: How to join two tables

Wed, 12 Feb 2014 05:33:23 GMT

Pasting Sujit's code.

SET ThousandSep=' ';

SET DecimalSep='.';

SET MoneyThousandSep=' ';

SET MoneyDecimalSep=',';

SET MoneyFormat='R # ##0,00;R-# ##0,00';

SET TimeFormat='hh:mm:ss TT';

SET DateFormat='YYYY/MM/DD';

SET TimestampFormat='YYYY/MM/DD hh:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Table1:

LOAD * INLINE [

EMP_ID

];

table2:

LOAD * INLINE [

EMP_ID1

];

Join(Table1)

LOAD

EMP_ID1 as EMP_ID,EMP_ID1

Resident table2;

DROP Table table2;

DATA:

LOAD

EMP_ID,EMP_ID1,

if(EMP_ID1<>null(),'Match','MisMatch') as MisMatch

Resident Table1;

DROP Table Table1;

Re: How to join two tables

sujeetsingh — Wed, 12 Feb 2014 05:39:13 GMT

Table1:

LOAD * INLINE [

EMP_ID

];

table2:

LOAD * INLINE [

EMP_ID1

];

Join(Table1)

LOAD

EMP_ID1 as EMP_ID,EMP_ID1

Resident table2;

DROP Table table2;

DATA:

LOAD

EMP_ID,EMP_ID1,

if(EMP_ID1<>null(),'Match','MisMatch') as MisMatch

Resident Table1;

DROP Table Table1;

Re: How to join two tables

sujeetsingh — Wed, 12 Feb 2014 05:40:40 GMT

it seems that RamKumar was able to open the file and he has provided my script what i am using to reload.

Thanks RamKumar

Re: How to join two tables

IAMDV — Wed, 12 Feb 2014 06:17:42 GMT

Hi Mani,

One more of doing this without using the Join Statement.

Table1:

LOAD * INLINE [

EMP_ID

];

table2:

LOAD * INLINE [

EMP_ID1

];

Temp:

LOAD EMP_ID AS My_Emp_ID, 'Table1' AS TableName Resident Table1;

Concatenate

LOAD EMP_ID1 AS My_Emp_ID, 'Table2' AS TableName Resident table2;

Final:

LOAD *

Where MyCount = 1;

LOAD Count(My_Emp_ID) AS MyCount, My_Emp_ID

Resident Temp

Group by My_Emp_ID;

Drop Table Temp;

Thanks,

Re: How to join two tables

Wed, 12 Feb 2014 06:19:50 GMT

simply try this

suppose you have two tables.

Table1:

load

RowNo() as key,

Emp_Id

from Table1;

join

Table2:

load

RowNo() as key,

Emp_Id as Emp_Id_new

from Table2;

OutputTable1:

load

Emp_Id as Emp_Id_output

resident Table1

where Emp_Id <> Emp_Id_new;

OutputTable2:

load

Emp_Id_new as Emp_Id_output

resident Table1

where Emp_Id <> Emp_Id_new;

Re: How to join two tables

Wed, 12 Feb 2014 06:41:57 GMT

Hi,

Qualify *;

unqualify 'EmpID_key';

Table1:

load Emp_ID as EmpId_key,* ;

inner join

Table2:

load Emp_ID as EmpId_key, * ;

Its show only those records which matched based on EmpId_key, remember after that join table1 and table2 colums are in same column line, also rename other column name of table2 which is same name in table1 (or use qualify and unqualify), other wise QV join other columns as a composit key (Syntathic key) which is same name in both tables.

Regards

Zain.

Re: How to join two tables

er_mohit — Wed, 12 Feb 2014 07:47:45 GMT

Try this

Table1:

LOAD rowno() as Key, * INLINE [

EmpID1

];

Join

Table2:

LOAD rowno() as Key,* INLINE [

EmpID2

];

New:

LOAD if(EmpID2<>EmpID1,EmpID2) as EmpID

Resident Table1;

join

LOAD if(EmpID2<>EmpID1,EmpID1) as EmpID

Resident Table1;

DROP Table Table1;

Re: How to join two tables

Fri, 14 Feb 2014 12:31:40 GMT

Hi All,

i want to load match and mismatch values to store separate column.How to do this,Please help me,

Re: Re: How to join two tables

sundarakumar — Fri, 14 Feb 2014 12:43:41 GMT

There si a work around,

PFA.

first qvd:

in script

one:

LOAD id
FROM
C:\Users\379790\Desktop\unmatch\src1.xlsx
(ooxml, embedded labels, table is Sheet1);

outer join
two:
LOAD id
FROM
C:\Users\379790\Desktop\unmatch\src2.xlsx
(ooxml, embedded labels, table is Sheet1);

NoConcatenate
//drop table one;
three:
LOAD id as ida,id
FROM
C:\Users\379790\Desktop\unmatch\src1.xlsx
(ooxml, embedded labels, table is Sheet1);

inner join
four:
LOAD id as id
FROM
C:\Users\379790\Desktop\unmatch\src2.xlsx
(ooxml, embedded labels, table is Sheet1);

store one into one.qvd;
store three into three.qvd;

this will create two qvd's one and two.

second qvd:

one:

LOAD id,
ida
FROM
C:\Users\379790\Desktop\unmatch\three.qvd
(qvd);

LOAD id
FROM
C:\Users\379790\Desktop\unmatch\one.qvd
(qvd)
where not exists(id);

This will result in the expected.

-Sundar

Re: Re: How to join two tables

sundarakumar — Fri, 14 Feb 2014 12:45:03 GMT

Match values can be easily obtained for an inner join .

-Sundar

Re: How to join two tables

Fri, 14 Feb 2014 12:55:12 GMT

Dear,

Create two map file one for table 1 and second for table 2, and after that apply first map first to second table and second map to first table, map flag value is match and unmatch.

then concate both tables and you can found match and unmatch values in seperate column.

I have did this in my fix assest application to find addition and deletion assest current and preivous month.

I will also try to send code.

Regards,

Zain.

Re: How to join two tables

Thu, 04 Dec 2014 14:33:00 GMT

Hi,

Tab1:

Empid	Name
101	irf
102	gho
	zee

Tab2 :

Empid	Name
101	irf
102	gho
103	zee

Script :

Tab1:

LOAD Empid,

Empid as Empid1,

Name

FROM

(ooxml, embedded labels, table is Tab1);

Right join

Tab2:

LOAD Empid,

Empid as Empid2,

Name

FROM

(ooxml, embedded labels, table is Tab2)

where not Exists(Empid1,Empid);

Result :

Empid1	Empid2	Name
	103	zee

Re: How to join two tables

ThornOfCrowns — Thu, 04 Dec 2014 14:36:30 GMT

Is this a question or answer?

Re: How to join two tables

Thu, 04 Dec 2014 15:26:35 GMT

aswer