topic Re: Dynamic value in QlikView

Dynamic value

simsondevadoss — Wed, 24 Dec 2014 09:11:45 GMT

Hi ,

I have a column called period which contains the value 19901.19902,19903,19911,19912,19913,19914,19915, 20011,20012.. so on.

I need to pass the first 5 value dynamically in the SQL statement and store it in the QVD.

Eg: SQL select ...

...

where Period = '$(Date)'

Thanks in advance.

Re: Dynamic value

tresB — Wed, 24 Dec 2014 09:27:00 GMT

May be you can try like:

Load

Max(-Field, 5)*-1 as FifthLowest

From <>;

Let vFifthLowest=Peek('FifthLowest');

Sql Select

...

Where Period <='$(vFifthLowest)' ;

Re: Dynamic value

Gysbert_Wassenaar — Wed, 24 Dec 2014 09:32:37 GMT

Something like this:

PeriodList:
LOAD 
  left(period_list, index(period_list,',',5)-1) as period_list;
LOAD 
  concat(period,',',period) as period_list
INLINE [
period
20013
19901
19902
20018
19903
19911
19912
19913
19914
19915
20011
20012
];

Let vPeriodList = peek('period_list');

drop table PeriodList;

SQL SELECT * FROM MyTable where Period in ('$(vPeriodList)');

Note, the inline load is just an example. It doesn't matter where the source data comes from. A load statement for a file or database source will work just as well as an inline load.

Re: Dynamic value

Wed, 24 Dec 2014 09:38:11 GMT

Please find the attached document

Re: Dynamic value

SunilChauhan — Wed, 24 Dec 2014 09:42:53 GMT

Load * inline [

fieldname

19901

19902

19903

19911

19912

19913

19914

19915

20011

20012

]where rowno()<5;

Re: Dynamic value

simsondevadoss — Wed, 24 Dec 2014 10:15:25 GMT

Thanks Gysbert it helps.

I also need to pass next five values and so on. store it into other qvd .

Since data is of huge volume and contains history of data. I need to split based on period on store it.

Re: Dynamic value

Gysbert_Wassenaar — Wed, 24 Dec 2014 10:32:24 GMT

It would be a lot easier to create a qvd for each period.

Periods:
SQL SELECT DISTINCT Period FROM MySourceTable;

FOR i = 1 TO NoOfRows('Periods')

    LET vPeriod = fieldvalue('Periods', $(i));

    MyTable
    SQL SELECT * FROM MySourceTable WHERE Period = $(vPeriod);

    STORE MyTable INTO $(vPeriod).qvd (qvd);
   
    DROP TABLE MyTable;

NEXT;

DROP Table Periods;

Re: Dynamic value

simsondevadoss — Wed, 24 Dec 2014 10:39:40 GMT

Yes. But i have totally 85 distinct period list.

Instead of storing it into 85 different qvd's. So i thought passing five values each time dynamically will be an better option.

Re: Dynamic value

Gysbert_Wassenaar — Wed, 24 Dec 2014 10:43:08 GMT

Personally I would store all the historical data in one qvd unless I really absolutely unavoidably needed to use qvd's with subsets of the total historical data.