topic Re: Re: Re: Re: Re: How we can display field row values as column headings in QlikView

How we can display field row values as column headings

Wed, 30 Jul 2014 13:57:56 GMT

Hello Every one,

I was trying to display field values as column headings( horizontal format) and that column field value as with "Trend" hyperlink.

If I click Trend hyperlink it should be display the line chart .

please see the attached images for your understand and suggest me how we can achieve it.

Thanks

Swathi

Re: How we can display field row values as column headings

Wed, 30 Jul 2014 14:00:38 GMT

Hi Swati,

You have not attached any images.

Thanks

Sabal

Re: Re: How we can display field row values as column headings

Wed, 30 Jul 2014 14:02:09 GMT

here it is

Re: How we can display field row values as column headings

JonnyPoole — Wed, 30 Jul 2014 14:40:21 GMT

I changed the expressions to text from link and then just formatted them as blue/underline to look like links.

When selected, getselectedcount(Metric) evaluates to 1. And with that you can control which metric shows in a detailed trend chart.

take a look at the example. you'll need to add conditional expressions depending on which metric is selected. also the trend chart is hidden unless there is only 1 selected metric.

help ?

Re: Re: How we can display field row values as column headings

Wed, 30 Jul 2014 15:14:16 GMT

Thanku Jonathan for your quick response,but I would like to display text "Trend" instead of digits.looks like below format

Output:

No Of recepts	Avg std Leadtime & AWLT(Combination of 2 fields here)	OTD%,Short Cycle & Request Met(Combination of 3 fields here)	P95Days(Late) & P5Days(Early)
Trend	Trend	Trend	Trend

Now if I click on Trend I would like to display the SUM of values in line chart.So is it possible to do it in qlikview.

Thanks

Swathi

Re: Re: Re: How we can display field row values as column headings

JonnyPoole — Wed, 30 Jul 2014 15:41:18 GMT

you can actually just type 'Trend' in the expression definition to show Trends. Attached is the updated example.

in the trend chart you can use SUM or any other expression to show the results you want.

But i couldn't build a trend chart according to your screenshot because I didn't find any data fields in your QVW to show a weekly numbers.

So for your 2nd question i'm not 100% sure what you want to show in the trend.

Re: Re: Re: Re: How we can display field row values as column headings

Wed, 30 Jul 2014 15:57:43 GMT

Thanku so much Jonathan ...looks we are almost near to our expected output for my 1st question.

but have some differences between expected output and your qvw output.

see the image for QVW output.

and

Expected output is combination of two or 3 fields as shown as previous mail.

So is it possible to merge two or three fields in single column ??

Thanks

Swathi

Re: Re: Re: Re: Re: How we can display field row values as column headings

JonnyPoole — Wed, 30 Jul 2014 16:28:50 GMT

For the 1st question i created a new 'Metric Category' field that buckets 1 or more of the buckets together based on your screenshot. I did this in the load script. The Data: table below represents the table your qlikview script produces today. Below that i have a new metric category table that loads in all the metrics and depending on the name creates a new 'metric category' tag.

Then i use the metric category in the dimension so you get the labels you want.

In the trend you can show 1 or more expressions in a line or combo chart depending on the metric category you select.

Data:

LOAD CRETD_BY,

CRETD_DT,

LAST_UPDTD_BY,

LAST_UPDTD_DT,

Metric,

MSR_ID,

PLANT_NAM,

POLE_NAM,

PRIOR_FW_VAL,

PRIOR_MONTH_VAL,

PRIOR_QTR_VAL,

PRIOR_YR_VAL,

SUPPLIER_FLG

FROM

(biff, embedded labels, table is Sheet1$);

MetricCategory:

load

if(Metric='NO OF RECEIPTS','NO OF RECEIPTS',

if(Metric='AVG. STD. LEAD TIME' or Metric='$ AWLT','AVG. STD. LEAD TIME & $ AWLT',

if(Metric='ON TIME DELIVERY %' or Metric='REQUEST MET %' or Metric='SHORT CYCLE %', 'ON TIME DELIVERY % & REQUEST MET % & SHORT CYCLE %',

if(Metric='P5 DAYS (EARLY)' or Metric='P95 DAYS (LATE)','P5 DAYS (EARLY) & P95 DAYS (LATE)'

)

) as MetricCategory,

Metric

resident Data;

exit script;

Re: Re: Re: Re: How we can display field row values as column headings

Thu, 31 Jul 2014 06:53:01 GMT

great thanku Jonathan ,looks good but is it possible to remove Metric Category column alone,I dont want to show it I want to hide it.

Let me work on my 2nd question,I believe for my 2nd question I need to add master calender table to my datamodel.

Thanks

Swathi

Re: Re: Re: Re: How we can display field row values as column headings

JonnyPoole — Thu, 31 Jul 2014 12:59:03 GMT

You can prevent the metric category from showing up in the current selections box. Would that help ?

To do that you need to set a hidden field prefix in the load script like this:

set HidePrefix='_' ;

And then when you load the MetricCategory , prefix it with an '_' like this:

MetricCategory:

load

if(Metric='NO OF RECEIPTS','NO OF RECEIPTS',

if(Metric='AVG. STD. LEAD TIME' or Metric='$ AWLT','AVG. STD. LEAD TIME & $ AWLT',

if(Metric='ON TIME DELIVERY %' or Metric='REQUEST MET %' or Metric='SHORT CYCLE %', 'ON TIME DELIVERY % & REQUEST MET % & SHORT CYCLE %',

if(Metric='P5 DAYS (EARLY)' or Metric='P95 DAYS (LATE)','P5 DAYS (EARLY) & P95 DAYS (LATE)'

)

) as _MetricCategory,

Metric

resident Data;

Re: Re: Re: Re: Re: How we can display field row values as column headings

Thu, 31 Jul 2014 13:50:09 GMT

ok thats fine Thanku so much.

Coming to the 2nd question If I click on trend I need to display line chart with dimention as FISCAL WEEK and expression as Sum(No Of Receipts).

For this I have added calender table to my datamodel and tried to create chart it but I am unable to succeed can you pls check once.

Thanks

Swathi

Re: Re: Re: Re: Re: How we can display field row values as column headings

JonnyPoole — Thu, 31 Jul 2014 14:05:34 GMT

I'm not sure ! You will have to tell me the following:

Which field in your data model shows multiple dates (or weeks) ?

Which field in your data model represents the receipt ID ?

THis wasn't obvious to me.

Re: Re: Re: Re: Re: Re: How we can display field row values as column headings

Thu, 31 Jul 2014 15:02:33 GMT

the field FISCAL_YEAR_WEEK_NUM filed of Calender table(attached table) shows the weeks .

Metric column represents different values (like No of receipts,AWLT,AVG STD.LEADTIME....etc)

thanks

Swathi

Re: How we can display field row values as column headings

JonnyPoole — Thu, 31 Jul 2014 15:18:40 GMT

ok. i joined the new data set to the existing on LAST_UPDTD_DT . i needed to wrap it in a date() function to do so. attached

But i don't see how i count the receipts. the metric column has 'no of receipts' for example, but which other field gives you the number for the receipts ? Also LAST_UPDTD_DT and fiscal date appear to be 2 different things. If you need to report metrics on a fiscal date (or week) basis, you'll need to add fiscal week to the main data table or add the number of receipts to the 'calendar' table.