topic Re: common field between two tables in QlikView

common field between two tables

Wed, 27 Aug 2014 11:50:19 GMT

i have below data in script

LOAD EMPNO,

ENAME,

JOB,

MGR,

date(HIREDATE,'MMM') as mon,

date(HIREDATE,'YYYY') as year,

SAL,

COMM,

DEPTNO,

"DATE_UPDATED";

SQL SELECT *

FROM SCOTT.EMP;

mon:

load * inline [

no, mon

1,Jan

2,Feb

3,Mar

4,Apr

5,May

6,June

7,Jul

8,Aug

9,Sep

10,Oct

11,Nov

12,Dec

];

as u can see above image

2.part - if i select the month - number are summing up fine.

3.part - if u select the month from inline table - number are not summing up fine.

4.part - data model

forst of all i want only distinct month in list box. but it is showing from both tables.

I havn't use join between two tables, if i use left outer join it is working fine.

How can i get the distinct month in list box with out using "left join"?????

thanks

Re: common field between two tables

ashfaq_haseeb — Wed, 27 Aug 2014 11:57:35 GMT

Hi,

Why you need inline table?

you can try something like this

LOAD EMPNO,

ENAME,

JOB,

MGR,

Month(HIREDATE) as mon,

num(month(HIREDATE)) as no,

year(HIREDATE) as year,

SAL,

COMM,

DEPTNO,

"DATE_UPDATED";

SQL SELECT *

FROM SCOTT.EMP;

Regards

ASHFAQ

Re: common field between two tables

Anonymous — Wed, 27 Aug 2014 12:00:36 GMT

The "months" fromthe inline table and from your data table does have different formats.

the inline month is text, while data table month is date.

If you format both as e.g. text the listbox should only show 12 monthnames as desired

on the other side: why you need the inline table anyway? what is the purpose?

Re: common field between two tables

Wed, 27 Aug 2014 12:30:11 GMT

Thanks Rudolf

it is working fine after changing the datatype for month column of inline table.

i have no assignment, not to use any where

i am just practicing

thanks for the to-the-point logic