Conditional Count If question

Mon, 20 May 2013 20:03:49 GMT

I have a table which I have loaded from a delimited text file.

The primary key is Rev-Trac which is an number field

and I have another field called TX which is a string field. I can have many transports to a single Rev-Trac number.

TRANSPORT_DATA_INT:

LOAD Num (@1) as [Rev-Trac#],
Num(@1) as [TX-Rev-Trac#],
@2 as TX_Version,
@3 as TX_Transport,
@4 as TX_Sequence
//count(num(@1)) as TX_Count

FROM
[http://fileNetworkLoaction/QV-TransportData.TXT]
(txt, codepage is 1252, explicit labels, delimiter is '|', msq, header is 1 lines, filters(
Remove(Col, Pos(Top, 1)),
Remove(Col, Pos(Top, 5)),
Remove(Row, RowCnd(CellValue, 1, StrCnd(null))),
Remove(Row, RowCnd(CellValue, 1, StrCnd(start, '---'))),
Remove(Row, RowCnd(CellValue, 1, StrCnd(equal

, 'Request')))
));

Once this table is loaded I than use a Left Join to run the aggregate function, Count against the Rev-Trac # field to get the # of times that each Rev-Trac# occurs.

LEFT

JOIN (TRANSPORT_DATA_INT)
LOAD [Rev-Trac#],
count([Rev-Trac#]) AS [TX_Count]

RESIDENT TRANSPORT_DATA_INT GROUP BY [Rev-Trac#];

Here is where it gets interesting. In some cases the Transport field can be blank. Since I am counting the number of Rev-Trac# occurances I get a value of one in these situations when I really should have a 0 since there is no Transport. So what I would like to do is write a script that updates the the count field so that where the Transport field IsNull the count field receives a 0 and where the Transport field is not Null it counts the Rev-Trac# occurances.

On a side note, one of the things that I did try was to run another Left Join adding a "final count"field where an If statement compared the fields but when it executes this code the script fails to to memory issues. So I need a way to execute this task using as little system resources as possible.

Any help that you qlikview experts can provide this novice would be appriciated! In advance, thanks!

Re: Conditional Count If question

Mon, 20 May 2013 21:09:19 GMT

The solution that I ended up using... Since the Transport field was s string field instead of using IsNull in an If statement I used the LEN function.

So essentially if the field was not null then the len of the transport field would be greater than 0.

So with some modification to my LEFT JOIN paragraph I ended up with the following to solve my issue and populate the table correctly

LEFT JOIN (TRANSPORT_DATA_INT)
LOAD [Rev-Trac#],

count(If(Len(TX_Transport)>0,[Rev-Trac#])) As

[TX_Count]

RESIDENT TRANSPORT_DATA_INT GROUP BY [Rev-Trac#];

topic Re: Conditional Count If question in QlikView

Conditional Count If question

Re: Conditional Count If question