topic Re: Count number based on Parameters in QlikView

Count number based on Parameters

Anonymous — Fri, 10 Nov 2017 08:18:19 GMT

Hi,

I am trying to find out how to make a count based on parameters read from e.g. an excel file.

I have the following scenario:

A transaction table where the users needs to know how many occurrencis there is from OldValue in Field1 to NewValue in Field2, this can obvioulsy change and I wanted to place the parameter in an excel file that will be imported.

I have not managed to find out how to count the number of occurencies in the transacton table

ParamterTable

Col1 Col2

OldRef1 NewRefA

OldRef2 NewRefB

TransactionTable

Col1 Col2 Col3 Col4 Col5

Trans1 Text1 OldRef1 NewRefA 1234

Trans2 Text2 OldRef1 NewRefA 12

Trans3 Text1 OldRef1 New 1111

Trans4 Text3 OldRef2 NewRefAB 1234

Trans5 Text2 OldRef2 RefA 1234

With the data above would I like to get a result of 2 for the first case(OldRef1 -> NewRefA) and 1 for the second(OldReF2 -> NewRefB)

Help would be appreciated or if I should do this n another way.

/Frank

Re: Count number based on Parameters

YoussefBelloum — Fri, 10 Nov 2017 08:35:22 GMT

Hi,

So basically, you just want to relaod data based on the PärameterTable, so here juste load these two lines only ?

Re: Count number based on Parameters

antoniotiman — Fri, 10 Nov 2017 08:55:51 GMT

May be this

ParamterTable:
LOAD * Inline [
Old, New
OldRef1, NewRefA
OldRef2, NewRefB];

TransactionTable:
LOAD *,Lookup('New','Old',Col3,'ParamterTable') as NewField Inline [
Col1 Col2 Col3 Col4 Col5
Trans1 Text1 OldRef1 NewRefA 1234
Trans2 Text2 OldRef1 NewRefA 12
Trans3 Text1 OldRef1 New 1111
Trans4 Text3 OldRef2 NewRefB 1234
Trans5 Text2 OldRef2 RefA 1234
] (delimiter is spaces);

and Expression Like this :

=Count(If(NewField=Col4,Col4))

Re: Count number based on Parameters

Anonymous — Fri, 10 Nov 2017 09:17:30 GMT

I need to oad the entire transaction table since there are a lot of oter thinf that needs to be investigated based on this data, This calculation is just one small part of several KPI's that I want to display.

The data is also used for investigations and so on.

Re: Count number based on Parameters

Anonymous — Fri, 10 Nov 2017 10:18:24 GMT

This got me further and I think I got this part to work.

/Frank

Re: Count number based on Parameters

Anonymous — Fri, 10 Nov 2017 11:40:37 GMT

So I got it to count as I wanted but have another small issue that I need to solve.

I have created a Chart-Table that looks like this:

ResultTable

ParameterFiled1 ParameterFiled2 ResultField1 ResultField2

OldRef1 NewRefA OldRef1 NewRefA

OldRef1 NewRefA OldRef1 Ref1

OldRef1 NewRefA OldRef1 Ref2

OldRef1 NewRefA OldRef1 Ref3

But I only want to display the first row where ParameterFiled1=ResultField1 and ParameterFiled2=ResultField2, I have tried with conditional, but that only supresses the columns not the row.

I haven't been working with Qlikview for a while and am feeling like a Noob.....

/Frank

Re: Count number based on Parameters

antoniotiman — Fri, 10 Nov 2017 14:37:50 GMT

Which are Your ParameterTable and TransactionTable ?

Re: Count number based on Parameters

Anonymous — Mon, 13 Nov 2017 08:08:48 GMT

I have namded them

TableName:

ParameterFile

Columns:

OldValue

NewValue

TableName:

TransactionTable

OldValue

NewValue

So in the above example would ParameterField1 and ParameterFiled2 come from the table ParameterFile and ResultField1 and ResultField2 from the transactiontable.

Re: Count number based on Parameters

antoniotiman — Mon, 13 Nov 2017 08:23:14 GMT

Where are Ref1/2/3 ?

Can You provide Tables with values (input and output) ?

Re: Count number based on Parameters

Anonymous — Mon, 13 Nov 2017 12:30:58 GMT

I have made a testapplication with a bit of data, the result I'm trying to get from the data that exist is a table containing

two rows

OldValue NewValue Count

OutStanding Matched 86

2011-09-13 2011-10-11 1

The other two rows

ParameterFile.OldValue ParameterFile.NewValue

Charges 55531V

2013-08-14 2015-08-14

do not exist in the itemAuditTrail table and should not be shown.