Dynamic value

[simsondevadoss]

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.

[tresesco]

May be you can try like:

Load

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

From <>;

Let vFifthLowest=Peek('FifthLowest');

Sql Select

          ...

Where Period <='$(vFifthLowest)' ;

[Gysbert_Wassenaar]

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.

[#4]

Please find the attached document

[SunilChauhan]

Load * inline [

fieldname

19901

19902

19903

19911

19912

19913

19914

19915

20011

20012

]where rowno()<5;

[simsondevadoss]

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.

[Gysbert_Wassenaar]

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;

[simsondevadoss]

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.

[Gysbert_Wassenaar]

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.