Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
bradhakrishnan
Contributor
Contributor

Straight Table - Comparison with Baseline

I have a Qlikview problem that I need to solve.

I have a table1 with has a pagename, date, position and pageviews.

I have another table2  which is the baseline table which has the baselineposition, avg.pageviews

Now I want to create a straight table which has the pagename, avg(position) as int, avg(pageviews) [all from table1] and then get the baseline avg. pageviews from table2 corresponding to avg(position) calculated from table1.

Is this possible?

Bharath.

Update: Added sample data and desired result straight table. hope this helps.

Message was edited by: bharath radhakrishnan

The solution provided by Oscar shows the correct number but the expression is blank when I open the dashboard in qlikview or in the web. It shows the numbers only when I refresh the dashboard. Any help is greatly appreciated. Attached the qlikview and source data excel files. Hope this helps.

Update2:

Found the solution!!!

I dont know why...but the issue seems to be with the FieldIndex function and not the FieldValue function.

So this is what I did.

In my query I sorted the baseline table by baseline position (ascending) and then I changed my calcuation to

FieldValue( 'baselineimpressions', Round( Avg( position ) ) )


This worked all the time and also showed the values when I accessed it in Access Point through my browser.


P.S.: I am still curious to find out why the FieldIndex didnt work with a calculation. It worked when I just hardcored an integer like FieldValue( 'baselineimpressions', FieldIndex( 'baselineposition', 1) )


Thanks to Oscar for providing the initial solution.

1 Solution

Accepted Solutions
oscar_ortiz
Partner
Partner

I believe this is what you are trying to accomplish.

263406.PNG

!

View solution in original post

9 Replies
AlbertoV
Partner
Partner

Can you share some sample data? and an example of the desired outcome?

oscar_ortiz
Partner
Partner

It sounds like something that you could accomplish using alternate states.  Can you send a screen shot of what you are looking to accomplish?

AlbertoV
Partner
Partner

Please find attached a qvw.

I hope it helps.

Cheers!

bradhakrishnan
Contributor
Contributor

Thanks for your reply. However I dont want to calculate the %avg position in the query, as there could be more dimensions in the future. I am trying to see if there is a solution where the tables are not linked and still do what I want to do. Am I being clear?

oscar_ortiz
Partner
Partner

I believe this is what you are trying to accomplish.

263406.PNG

!

View solution in original post

bradhakrishnan
Contributor
Contributor

Thank you Oscar...

YOU ARE THE MAN!!!!

bradhakrishnan
Contributor
Contributor

Oscar,

One small issue though. The values dont show up unless I reload the dashboard. Also when I open it in the browser the field is blank.

Should I do anything to make it show up all the time?

bradhakrishnan
Contributor
Contributor

Found the solution!!!

I dont know why...but the issue seems to be with the FieldIndex function and not the FieldValue function.

So this is what I did.

In my query I sorted the baseline table by baseline position (ascending) and then I changed my calcuation to

FieldValue( 'baselineimpressions', Round( Avg( position ) ) )


This worked all the time and also showed the values when I accessed it in Access Point through my browser.


P.S.: I am still curious to find out why the FieldIndex didnt work with a calculation. It worked when I just hardcored an integer like FieldValue( 'baselineimpressions', FieldIndex( 'baselineposition', 1) )


Thanks to Oscar for providing the initial solution.

oscar_ortiz
Partner
Partner

Sorry for not getting back to you sooner, Friday's are travel days and I usually try to stay away from work type issues on the weekends.

So the FieldIndex should read the position of the field value by load order.  In the example that I provided QlikView read the second table in load order and everything was fine.  Based on the expression for rounding the Average position I returned the proper value and everything worked out.  You may want to check the results of the calculation to make sure it is returning an integer that will match your baseline position field.

From Help:

FieldIndex(fieldname , value )

Returns the position of the field value value in the field fieldname (by load order). If value cannot be found among the field values of the field fieldname, 0 is returned. Fieldname must be given as a string value, e.g. the field name must be enclosed by single quotes.