topic Re: Loading in certain records depending on value in QlikView

Loading in certain records depending on value

Fri, 21 Nov 2014 14:33:43 GMT

I have a database file that contains

AssetID Sequence BookID

I am currently picking up the row if the BookID is 1.

However there are some records that will have the same AssetID and BookID is 1 but the sequence would be different.

I need the row to be picked up if the Sequence is 0 and BookID is 1 but if there is a sequence 1, 2, etc then I need to pick these rows up instead of the record with the sequence 0.

Here's what the data would look like coming in:

AssetID	Sequence	BookID	PlaceServ	AcqValue
5456	0	0	00:00.0	188945.46
5456	1	0	00:00.0	178453
5456	2	0	00:00.0	10492.46
5456	0	1	00:00.0	188945.46
5456	1	1	00:00.0	178453
5456	2	1	00:00.0	10492.46

So in this case I would need the last two rows loaded and not the third one.

How could I go about doing this at time of load?????

Re: Loading in certain records depending on value

Fri, 21 Nov 2014 15:34:54 GMT

Will book ID always be 1 or 0?

Re: Loading in certain records depending on value

Fri, 21 Nov 2014 16:44:56 GMT

It could be 1 - 6 but I only need to pull bookID = 1

It needs to pull the bookid = 1

If there is just a record with 0 sequence, I need to pull that row for that assetid

but if there is a sequence 1 or 2 with bookid = 1 then I need to pull those rows and not pull the one with 0 sequence

Re: Loading in certain records depending on value

Mon, 24 Nov 2014 13:58:57 GMT

Sorry only just had a chance to look at this.

I created a field in the script against AssetID/BookID as an indicator weather that asset ID has a Bookid = 1 and if so if there sequences present greater than 1. I then use an If in the chart expression to only show the row with the logic you described.

See attached

Re: Loading in certain records depending on value

Tue, 02 Dec 2014 21:03:50 GMT

Carly,

This is what I have but I'm still getting an error when trying to load:

book_parts:

left join(assets)

LOAD CompanyID,

AssetID,

Sequence,

BookID,

PlaceServ,

AcqValue,

EstimatedLife,

CurrentPrLifeDate as BegYrDepr,

CurrentYearToDate as CurYrDepr,

CurrentLifeToDate as LifeDepr,

CurrentNetValue,

CurrentCostBasis,

CurrentDeprFlags;

SQL SELECT CompanyID,

AssetID,

Sequence,

BookID,

PlaceServ,

AcqValue,

EstimatedLife,

CurrentPrLifeDate,

CurrentYearToDate,

CurrentLifeToDate,

CurrentNetValue,

CurrentCostBasis,

CurrentDeprFlags

FROM "Sage_FAS".dbo.BookParts

where BookID=1;

Left Join(book_parts)

LOAD CompanyID,

AssetID,

Sequence,

BookID,

IF( BookID = 1 AND MAX(Sequence)= 0, '0',

IF( BookID = 1 AND MAX(Sequence)> 0 , '>0', null()))as SeqRange

Resident book_parts

group by CompanyID, AssetID, BookID;

Not sure why I'm having a problem here. Can you help with this????

I get a table not found error

Re: Loading in certain records depending on value

Wed, 03 Dec 2014 08:36:23 GMT

remove sequence from the resident load or include it in the group by.