topic Re: How to display dynamic straight table ? in QlikView

How to display dynamic straight table ?

Sun, 14 Jun 2015 16:39:29 GMT

Hi,

I create straight table in qlikview . I want to display my straight table dynamically according to my selections. like this

If I select march then display my straight table Jan feb mar.like below.

Listbox
Jan
Feb
Mar
apr
may
jun

Straight table.

Country	Jan	Feb	mar
India	12000	15000	20000
Uk	15000	20000	25000
Aus	20000	25000	30000

If I select Feb then display Like this :it show only jan and feb .

country	Jan	Feb
India	12000	15000
Uk	15000	20000
aus	20000	25000

I wrote condition like this =WildMatch(Concat(month,'/'),'Jan') but It does not work. pls tell me out how display straight table like above

Thankyou,

Regards

madhu

Re: How to display dynamic straight table ?

MarcoWedel — Sun, 14 Jun 2015 16:54:55 GMT

Hi,

please explain, how your straight table shows month field values in columns?

Do you have seperate expressions (per month) or did you mean a pivot table instead of a straight table?

thanks

regards

Marco

Re: How to display dynamic straight table ?

maxgro — Sun, 14 Jun 2015 17:02:31 GMT

see attached .qvw

Re: How to display dynamic straight table ?

Sun, 14 Jun 2015 17:08:06 GMT

Thank you Massimo grossi.

Re: How to display dynamic straight table ?

Sun, 14 Jun 2015 17:10:37 GMT

I use personal edition .I can not open your attached qvw pls send screen shots or explain with small example .

Regards

madhu

Re: How to display dynamic straight table ?

sunny_talwar — Sun, 14 Jun 2015 17:12:13 GMT

Do you want something like the attached? (PFA)

Best,

Sunny

Re: How to display dynamic straight table ?

sunny_talwar — Sun, 14 Jun 2015 17:16:58 GMT

Since you use personal edition try this:

Script used:

Table:

LOAD * Inline [

Country, Month, Value

India, Jan, 12000

India, Feb, 15000

India, Mar, 20000

UK, Jan, 15000

UK, Feb, 20000

UK, Mar, 25000

Aus, Jan, 20000

Aus, Feb, 25000

Aus, Mar, 30000

];

Straight Table with

Dimension: Country

Expression: 1) =Sum({<Month = {'Jan'}>} Value)

2) =Sum({<Month = {'Feb'}>} Value)

3) =Sum({<Month = {'Mar'}>} Value)

With hide and show condition for the expressions

Expression: 1) =SubStringCount(Concat(DISTINCT Month, '|'), 'Jan') = 1 or

SubStringCount(Concat(DISTINCT Month, '|'), 'Feb') = 1 or

SubStringCount(Concat(DISTINCT Month, '|'), 'Mar') = 1

2) =SubStringCount(Concat(DISTINCT Month, '|'), 'Feb') = 1 or

SubStringCount(Concat(DISTINCT Month, '|'), 'Mar') = 1

3) =SubStringCount(Concat(DISTINCT Month, '|'), 'Mar') = 1

Image of where they need to go:

I hope this helps.

Best,

Sunny

Re: How to display dynamic straight table ?

maxgro — Sun, 14 Jun 2015 17:17:12 GMT

Replace my dim (product) with your (country) and MonthNames

SET DateFormat='DD/MM/YYYY';

SET MonthNames='gen;feb;mar;apr;mag;giu;lug;ago;set;ott;nov;dic';

a: load

pick(match(floor(rand()*6+1),1,2,3,4,5,6),'car','bike','bus','plane','train','boat') as product,

'T' & ceil(month(d)/3) as q,

year(d) as y,

date(d) as d,

dual(month(d), num(month(d))) as m,

load

floor(today() -1000 + rand()*2000) as d,

rand()*100-rand()*100 as v

AutoGenerate 10000;

Re: How to display dynamic straight table ?

Sun, 14 Jun 2015 17:20:12 GMT

Thankyou marco wedel.I take year 2001 and months jan, feb,march,apr,may. and I wrote expression in conditional box in expression tab like this.

=wildmatch(concat(month,'/')'jan')

=wildmatch(concat(month,'/')'feb')

=wildmatch(concat(month,'/')'mar')

=wildmatch(concat(month,'/')'apr')

when I select year 2001 and month jan I works correctly.but when I select year 2001 and months:jan,feb,march that time I does not display months(it does not work properly) pls tell me out .how to display straight table based on selections pls help me Marco wedel.

Regards

madhu

Re: How to display dynamic straight table ?

Sun, 14 Jun 2015 17:23:25 GMT

Thankyou sunindia.

Re: How to display dynamic straight table ?

Sun, 14 Jun 2015 17:24:12 GMT

Thankyou massimo grossi.

Re: How to display dynamic straight table ?

maxgro — Sun, 14 Jun 2015 17:27:23 GMT

if you get the answer, please close the discussion, thanks

Re: How to display dynamic straight table ?

MarcoWedel — Sun, 14 Jun 2015 22:54:51 GMT

Hi,

you already received two replies that answered your question to the point.

But just to make sure, that you are aware you are asking for a solution that lets a straight table imitate the function of a pivot table, another solution using a pivot instead of a straight table:

drag the month dimension to the top right corner.

Please let us know, if there are reasons you prefer a straight table instead.

hope this helps

regards

Marco

Re: How to display dynamic straight table ?

Mon, 15 Jun 2015 08:08:03 GMT

Thank you marco wedel.

Re: How to display dynamic straight table ?

Mon, 15 Jun 2015 09:00:36 GMT

Neetu_Singh‌

VERY NICE SUNINDIA

Re: How to display dynamic straight table ?

Mon, 15 Jun 2015 10:01:21 GMT

Hello marco wedel.

I tried as it is what you said but It does not work. see my data.

step1: I select pivot table

Step2: I take 2 dimensions

Step 3: I write expression in expression tab.

Step 4: I take this data

but when I select month in list box I doesnot work. see this image.

pls tell me marco wedel where did I do mistake .pls help me

Thankyou,

Regards

madhu

Re: How to display dynamic straight table ?

MarcoWedel — Mon, 15 Jun 2015 10:45:09 GMT

you did not load your months as dual values, i.e. there has to be an underlying numerical value to work properly.

Try to generate your months in the script e.g. like this

tabMonths:
LOAD Month(Date#(RecNo(),'M')) as month
AutoGenerate 12;

hope this helps

regards

Marco

Re: How to display dynamic straight table ?

sunilkumarqv — Mon, 15 Jun 2015 11:02:24 GMT

Check this please its really solve your needs .

Dynamically Selecting Timeframes

Re: How to display dynamic straight table ?

Mon, 15 Jun 2015 14:57:09 GMT

Thank you very much Marco wedel .thank you so much marco wedel .

Re: How to display dynamic straight table ?

Mon, 15 Jun 2015 16:39:26 GMT

Hello Marco wedel.I followed your instructions but it is not work properly according to my selections. the data disply in pivot table like this .

List box:

Step1: pivot table

step2 :

Step3:

Script:

But it is not work properly . pls help me

when I select feb It display only feb data.

Thankyou,

Regards

madhu