Count help

Fri, 17 May 2013 18:29:44 GMT

Greetings,

I have a situation where I need to load a table and provide a column with the number of Rev-Trac# values found for each Rev-Trac# value in the table.

Example with count result (TX_Count):

Rev-Trac # TX_Version TX_Transport TX_Sequece TX_Count

1111 2 ABCK901222 10 3

1111 3 ABCK901225 20 3

1111 4 ABCK901239 30 3

2222 2 ABCK901255 10 1

3333 2 ABCK901251 20 2

3333 3 ABCK901249 10 2

Here is my scripting to load the table into memory...

TRANSPORT_DATA_INT:

LOAD

Num

(@1) as [Rev-Trac#],
          @2 as TX_Version,
          @3 as TX_Transport,
          @4 as TX_Sequence

FROM
[http://lpapp1/Data/corp/comm/fhit.nsf/0/440a3dbe3be9e20e852573fd0050b6c8/$FILE/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')))
));

I have attempted adding an addition load field of "Count(@1) as TX_COUNT and adding the group by statement after the loaction of the file, but have not had much luck. Does anyone know what I need to do and what the proper script should be to get the Rev-Trac# counts?

Re: Count help

swuehl — Fri, 17 May 2013 20:23:09 GMT

Try adding this to your load script:

LEFT JOIN (TRANSPORT_DATA_INT)

LOAD [Rev-Trac#],

Count([TX_Version]) AS [TX_Count]

RESIDENT TRANSPORT_DATA_INT GROUP BY [Rev-Trac#];

Re: Count help

Mon, 20 May 2013 19:46:21 GMT

Worked like a charm! Thanks for the help swuehl.

So that I understand... One would use the LEFT JOIN to ADD another column to an existing table. Then when instructing the join where it is getting it's data from I have to use the RESIDENT call to call the already exisitng table. Does that sound about right???

Re: Count help

swuehl — Tue, 21 May 2013 21:16:52 GMT

Yes, I think that's correct.

Instead of the resident table, you can get the data from the original input source or a qvd, of course.

topic Re: Count help in QlikView

Count help

Re: Count help

Re: Count help

Re: Count help