Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
newhere2014
Contributor II
Contributor II

where clause in script

Hi all,

    I need Month condition for a WHERE clause in QV script. Month value is stored in a excel sheet. How can I use Month value from a excel sheet? Thanks.

the script is like this:

Load Year,

          Month,

          Sales

from table1

where Month > x;

x is a value stored in the excel sheet,which is changed manually monthly.

1 Solution

Accepted Solutions
Not applicable

 

temp:
LOAD Month
FROM

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

LET VTest=Peek('Month');


LOAD Name,
     Month,
     Sales
FROM

(biff, embedded labels, table is [Sheet1$])
WHERE(Month > $(VTest) );
DROP Table temp;

View solution in original post

6 Replies
TKendrick20
Partner
Partner

Hard code the x value into the QV script and change it there manually rather than on the excel sheet? Otherwise you could make the x value its own column in the excel sheet.

newhere2014
Contributor II
Contributor II
Author

Hard code is not an option for now.

Right now, x value is stored in excel as a column. But how to use it in script? Thanks

TKendrick20
Partner
Partner

Could you provide a sample of how data is loaded into table1?

It makes a huge difference if the Month values are January, February, March or 01, 02, 03.

newhere2014
Contributor II
Contributor II
Author

This is my code, but it can not run.

CloseMonth:

LOAD * INLINE [

    Month_1

    5

];

SET vMonth=Month_1;

T1:

LOAD * INLINE [

    Month, Sales

    1, 45

    2, 5

    3, 63

    4, 232

    5, 5

    6, 445

    7, 34

    8, 65

];

load Month as M,

    Sales

Resident T1

where Month>$(vMonth);   

drop table T1;

Not applicable

 

temp:
LOAD Month
FROM

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

LET VTest=Peek('Month');


LOAD Name,
     Month,
     Sales
FROM

(biff, embedded labels, table is [Sheet1$])
WHERE(Month > $(VTest) );
DROP Table temp;

View solution in original post

newhere2014
Contributor II
Contributor II
Author

This code works.  Peek function solved my problem.

Thanks all of you.