topic How to delete rows from a table? in QlikView

How to delete rows from a table?

denwo2010 — Fri, 17 Sep 2010 11:38:55 GMT

Hi,

I have two tables "Leavers" and "Calendar", I want to delete from the Calendar table any rows that has dates for that personid after there temination date but not sure how to do this in Qlikview? Can I do all this before the qvd is created for the Calendar ? I can do this in sql as per below but want to do this in Qlikview:

delete p
From Calendar p inner join Leavers l
on p.[Person Id] = l.PersonId
where
p.[Person Date]>l.TerminationDate

Thanks

How to delete rows from a table?

renjithpl — Fri, 17 Sep 2010 11:45:00 GMT

hey denwo, can you just send a sample of your calendar table, for further clarity. thanks

How to delete rows from a table?

denwo2010 — Fri, 17 Sep 2010 12:02:47 GMT

Here you go, Thanks

How to delete rows from a table?

Fri, 17 Sep 2010 12:40:04 GMT

F�rstly we are focusing data that you are trying to catch ( p.[Person Date]<l.TerminationDate) on the first table and than �nner join with your calendar table (PersonId, [Person Date])

try that code ;


TABLE1:
LOAD PersonId,
 [Person Date]
FROM
C:\Users\saltinbilek\Desktop\Calendar.qvd
(qvd);
inner join
LOAD PersonId,
 TerminationDate
FROM
C:\Users\saltinbilek\Desktop\Leavers.qvd
(qvd);


Calendar:
NOCONCATENATE LOAD PersonId,[Person Date]
RESIDENT TABLE1
where ([Person Date])<(TerminationDate) ;
INNER JOIN (Calendar) LOAD PersonId,
 CalendarKey,
 [Person Date],
 [Eng Week],
 [Eng Month],
 [Eng Year],
 [Eng Day of Week],
 [Std Hours],
 [Avail Day Flag],
 HOLS,
 UNAV,
 DOCT,
 SICK,
 SITE,
 TRAIN,
 [Min Arrive Time],
 [Max Leave Time],
 [Worked Hours],
 [Worked Day Flag],
 [Extra Day Flag],
 [Extra Day Hours],
 [Unavailable Days],
 [Total Unavail Hours],
 [Paid Time],
 [First Name],
 [Last Name],
 [Person Type],
 [Person Group],
 [Person Class],
 EmployedFlag,
 [Engineer OSG B],
 [Engineer OSG C],
 [Engineer OSG D],
 PersonCategory
FROM
C:\Users\saltinbilek\Desktop\Calendar.qvd
(qvd);

DROP TABLE TABLE1;

Leavers:
LOAD PersonId,
 TerminationDate
FROM
C:\Users\saltinbilek\Desktop\Leavers.qvd
(qvd);

How to delete rows from a table?

denwo2010 — Fri, 17 Sep 2010 13:35:52 GMT

Hi, Sorry it does not seem to work? I still want it to show dates till the 30/09/2010 for the other personid if they have not left, for some reason it is not going any further than the date 10/09/2010

How to delete rows from a table?

Fri, 17 Sep 2010 14:32:44 GMT

sorry my mistake ...;

try that one ;



TABLE1:
LOAD PersonId,
 [Person Date]
FROM
C:\Users\saltinbilek\Desktop\Calendar.qvd
(qvd);
inner join
LOAD PersonId,
 TerminationDate
FROM
C:\Users\saltinbilek\Desktop\Leavers.qvd
(qvd);


Calendar:
NOCONCATENATE LOAD PersonId&[Person Date] AS CA_KEY
RESIDENT TABLE1
where ([Person Date])>(TerminationDate) ;

LOAD PersonId,
 CalendarKey,
 [Person Date],
 [Eng Week],
 [Eng Month],
 [Eng Year],
 [Eng Day of Week],
 [Std Hours],
 [Avail Day Flag],
 HOLS,
 UNAV,
 DOCT,
 SICK,
 SITE,
 TRAIN,
 [Min Arrive Time],
 [Max Leave Time],
 [Worked Hours],
 [Worked Day Flag],
 [Extra Day Flag],
 [Extra Day Hours],
 [Unavailable Days],
 [Total Unavail Hours],
 [Paid Time],
 [First Name],
 [Last Name],
 [Person Type],
 [Person Group],
 [Person Class],
 EmployedFlag,
 [Engineer OSG B],
 [Engineer OSG C],
 [Engineer OSG D],
 PersonCategory,
 PersonId&[Person Date] AS CA_KEY
FROM
C:\Users\saltinbilek\Desktop\Calendar.qvd
(qvd) WHERE NOT EXISTS(CA_KEY,PersonId&[Person Date]);

DROP TABLE TABLE1;
DROP TABLE Calendar;

Leavers:
LOAD PersonId,
 TerminationDate
FROM
C:\Users\saltinbilek\Desktop\Leavers.qvd
(qvd);

How to delete rows from a table?

denwo2010 — Fri, 17 Sep 2010 14:59:14 GMT

Hi,

Thanks so far!

Nearly there and might use this one in the future!

But it is still not showing any dates past 16/09/2010, I want this to go to the last date which is 30/09/2010.

Thanks

How to delete rows from a table?

ivandrago — Fri, 17 Sep 2010 18:44:17 GMT

why don't you do a autonumber?

How to delete rows from a table?

denwo2010 — Fri, 17 Sep 2010 19:10:52 GMT

Sorry what do you mean?

Basically it is working but want the dates to carry on till the 30/09/2010