Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
So basically, you just want to relaod data based on the PärameterTable, so here juste load these two lines only ?
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))
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.
This got me further and I think I got this part to work.
/Frank
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
Which are Your ParameterTable and TransactionTable ?
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.
Where are Ref1/2/3 ?
Can You provide Tables with values (input and output) ?
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.