Display same EMPNO values in a single row

[qlikviewwizard]

Hi All,

I am unable to show same EMPNO values into one row.

EMPNO2 has two rows in the screen. But I want to show in a single row.

How to achieve this?

Please help.

Thanks in advance.

[datanibbler]

Hi,

you can aggregate by EmpNo, then you will have only one - but keep in mind:

- That is an aggregation, so you need a GROUP BY

- That is (imagine) as if you take little sticks and put them in heaps, so the more criteria (= fields) you look at, the more
   heaps you have

=> EmpNo2 has different values in some fields in the two rows, so you must not include those fields in the aggregation, else you will again get two lines.

HTH

[avinashelite]

Hi ,

You can try using the Pivot table , here you will not get two records for the same EMP . That could solve your problem

[rajni_batra]

Hi ,

You need to correct your data first either by Aggreation or other way. Your dept is blank for one of the ID but you have transnational value against the same.

if possible share your data model.

Thanks,

Rajni batra

[jonathandienst]

Is this in a pivot table? The problem is you have some entries on your data without a value in Dept.

It would be best to fix this in the source data or in the load script if that is not possible. I you must fix it in the front end, then you will not be able to use Dept as a dimension. You could use it as an expression (eg Concat(Dept, ',')), but you will get a column for each quarter.

[Anonymous]

Hi,

Try Pivot table that will serve your purpose .It will group records which are same . 

[buzzy996]

u can try with calculated dimension on employee number,hopes tht helps.

[Digvijay_Singh]

It looks like data coming from different source and combined manually. It always better to clean data before we try to apply analytical functions and representation. Please share your data model to clarify things as others also suggested. Thanks.

[qlikviewwizard]

Hi all,

I am using the below script.

Please help me.

DEPT:
LOAD DEPTNO,
     DNAME
FROM
EMPLOYEE.xlsx
(ooxml, embedded labels, table is DEPT);
EMP:
LOAD EMPNO,
     ENAME,
     DEPTNO,
     JOB,
     DATE(HIREDATE) AS HIREDATE,
     DATE(LASTDATE) AS LASTDATE,
     INCHARGE2,
     INCHARGE1
FROM
EMPLOYEE.xlsx
(ooxml, embedded labels, table is EMP);
LEFT JOIN (EMP)
SAL:
LOAD *,QUARTER &'-'&YEAR AS QUARTERYEAR;
LOAD EMPNO,
     YEAR,
     MONTH,
     QUARTER,
     DATE(STARTDATE) AS STARTDATE,
     SAL
FROM
EMPLOYEE.xlsx
(ooxml, embedded labels, table is SAL);

COMM:
LOAD YEAR,
     'Q' & QUARTER AS QUARTER,
     EMPNO,
     COMM
FROM
EMPLOYEE.xlsx
(ooxml, embedded labels, table is COMM);


Temp:
LOAD INCHARGE1 AS EMPNO,  
     SAL AS INCHARGE1SAL,
     QUARTERYEAR

RESIDENT EMP


WHERE Len(Trim(INCHARGE1)) > 0
;
Temp:
LOAD INCHARGE2 AS EMPNO,  
     SAL AS INCHARGE2SAL,
     QUARTERYEAR
RESIDENT EMP
WHERE Len(Trim(INCHARGE2)) > 0 or ISNULL(LASTDATE)=-1
;

[qlikviewwizard]

Hi jontydkpi

I am using pivot table.