topic Re: Fetching selected data and Storing same data in QVD in QlikView

Fetching selected data and Storing same data in QVD

deepakqlikview_123 — Fri, 02 May 2014 07:18:13 GMT

Hello Team,

Below is my code for fetching data from database

WorkerAttendance:

LOAD "DEPARTMENT_NAME",

"SHORT_NAME",

if(DEPARTMENT_NAME='FABRICATION SHOP','FAB',

if(DEPARTMENT_NAME='PAINT SHOP' ,'PAINT',

if(DEPARTMENT_NAME='MACHINE SHOP' or DEPARTMENT_NAME='SUB ASSEMBLY & ASSEMBLY','ASSM'))) as WorkerDeptName,

if(PLANT='PVVP','3-4 Wheeler',

if(PLANT='PV2P','2 Wheeler',

if(PLANT='PVEP','Engine'))) as Flag,

CATEGORY,

"SHIFT_NUMBER" as WorkerShift,

"MAN_ON_ROLE",

PRESENT,

// "PUNCH_DATE",

MakeDate(year(PUNCH_DATE),month(PUNCH_DATE),day(PUNCH_DATE)) as PUNCH_DATE1,

date(PUNCH_DATE,'DD-MM-YYYY hh:mm:ss') as PUNCH_DATE,

Year(PUNCH_DATE) as PUNCH_Year,

month(PUNCH_DATE) as PUNCH_Month,

day(PUNCH_DATE) as PUNCH_Day,

num(month(PUNCH_DATE)) as PUNCH_Month1,

hour(PUNCH_DATE)&':'&minute(PUNCH_DATE)&':'&second(PUNCH_DATE) as PUNCH_time1,

(if(PLANT='PVVP','3-4 Wheeler',

if(PLANT='PV2P','2 Wheeler',

if(PLANT='PVEP','Engine'))))&'-'&(if(DEPARTMENT_NAME='FABRICATION SHOP','FAB',

if(DEPARTMENT_NAME='PAINT SHOP' ,'PAINT',

if(DEPARTMENT_NAME='MACHINE SHOP' or DEPARTMENT_NAME='SUB ASSEMBLY & ASSEMBLY','ASSM'))))&'-'&SHIFT_NUMBER as WorkerKey;

SQL SELECT *

FROM HRD."DAILY_ATTENDANCE_INTERFACE";

Now I want to retrieve only those records where year =2014 and store those records in qvd.

Can you please suggest how to proceed for the same.

thanks

Re: Fetching selected data and Storing same data in QVD

Fri, 02 May 2014 07:23:27 GMT

Only2014:

load *

resident WorkerAttendance

where Punch_year=2014;

Store Only2014 into attendance2014.qvd(qvd);

drop table Only2014;

Re: Fetching selected data and Storing same data in QVD

MK_QSL — Fri, 02 May 2014 07:25:41 GMT

WorkerAttendance:

LOAD "DEPARTMENT_NAME",

"SHORT_NAME",

if(DEPARTMENT_NAME='FABRICATION SHOP','FAB',

if(DEPARTMENT_NAME='PAINT SHOP' ,'PAINT',

if(DEPARTMENT_NAME='MACHINE SHOP' or DEPARTMENT_NAME='SUB ASSEMBLY & ASSEMBLY','ASSM'))) as WorkerDeptName,

if(PLANT='PVVP','3-4 Wheeler',

if(PLANT='PV2P','2 Wheeler',

if(PLANT='PVEP','Engine'))) as Flag,

CATEGORY,

"SHIFT_NUMBER" as WorkerShift,

"MAN_ON_ROLE",

PRESENT,

// "PUNCH_DATE",

MakeDate(year(PUNCH_DATE),month(PUNCH_DATE),day(PUNCH_DATE)) as PUNCH_DATE1,

date(PUNCH_DATE,'DD-MM-YYYY hh:mm:ss') as PUNCH_DATE,

Year(PUNCH_DATE) as PUNCH_Year,

month(PUNCH_DATE) as PUNCH_Month,

day(PUNCH_DATE) as PUNCH_Day,

num(month(PUNCH_DATE)) as PUNCH_Month1,

hour(PUNCH_DATE)&':'&minute(PUNCH_DATE)&':'&second(PUNCH_DATE) as PUNCH_time1,

(if(PLANT='PVVP','3-4 Wheeler',

if(PLANT='PV2P','2 Wheeler',

if(PLANT='PVEP','Engine'))))&'-'&(if(DEPARTMENT_NAME='FABRICATION SHOP','FAB',

if(DEPARTMENT_NAME='PAINT SHOP' ,'PAINT',

if(DEPARTMENT_NAME='MACHINE SHOP' or DEPARTMENT_NAME='SUB ASSEMBLY & ASSEMBLY','ASSM'))))&'-'&SHIFT_NUMBER as WorkerKey

WHERE YEAR(PUNCH_DATE) = '2014';

SQL SELECT *

FROM HRD."DAILY_ATTENDANCE_INTERFACE";

STORE WorkerAttendancelk into 'WorkerAttendance.qvd' (QVD);

Re: Fetching selected data and Storing same data in QVD

Fri, 02 May 2014 07:26:55 GMT

hi deepak it is possible with what ramkumar told try like this

Re: Fetching selected data and Storing same data in QVD

deepakqlikview_123 — Fri, 02 May 2014 07:34:36 GMT

Hi All,

PFA

It is giving me error as table not found.

Thanks

Re: Fetching selected data and Storing same data in QVD

its_anandrjs — Fri, 02 May 2014 07:56:27 GMT

Remove single cots from store like

STORE WorkerAttendancelk into 'WorkerAttendance.qvd' (QVD);

You use

STORE WorkerAttendance into WorkerAttendance.qvd (QVD);

Re: Fetching selected data and Storing same data in QVD

deepakqlikview_123 — Fri, 02 May 2014 08:42:53 GMT

Thanks Manish.

It worksas as per exceptation.