Count number based on Parameters

[Anonymous]

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

[YoussefBelloum]

Hi,

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

[antoniotiman]

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))

[Anonymous]

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.

[Anonymous]

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

/Frank

[Anonymous]

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

[antoniotiman]

Which are Your ParameterTable and TransactionTable ?

[Anonymous]

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.

[antoniotiman]

Where are Ref1/2/3 ?

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

[Anonymous]

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.