Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikviewwizard
Master II
Master II

Display same EMPNO values in a single row

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.

Capture.JPG

1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

Hi,

Try this script to fix the DEPTNO issue

DEPT:

LOAD DEPTNO,

     DNAME

FROM

EMPLOYEE.xlsx

(ooxml, embedded labels, table is DEPT);

EMPDEPT:

LOAD DISTINCT

EMPNO,

DEPTNO

FROM

EMPLOYEE.xlsx

(ooxml, embedded labels, table is EMP)

WHERE Len(Trim(DEPTNO)) > 0;

EMP:

LOAD EMPNO,

     ENAME,     

     JOB,

     DATE(HIREDATE) AS HIREDATE,

     DATE(LASTDATE) AS LASTDATE,

     INCHARGE2,

     INCHARGE1

FROM

EMPLOYEE.xlsx

(ooxml, embedded labels, table is EMP);

LEFT JOIN

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;

Regards,

jagan.

View solution in original post

14 Replies
datanibbler
Champion
Champion

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

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
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable

Hi,

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

buzzy996
Master II
Master II

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
Master II
Master II
Author

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

;

Capture.JPG

qlikviewwizard
Master II
Master II
Author

Hi jontydkpi


I am using pivot table.