Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
MVP & Luminary
MVP & Luminary

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

Highlighted

Hi ,

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

Highlighted
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

Highlighted

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

Hi,

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

Highlighted
Master II
Master II

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

Highlighted
Master III
Master III

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.

Highlighted
Master II
Master II

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

Highlighted
Master II
Master II

Hi jontydkpi


I am using pivot table.