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: 
qvhlaold0
Contributor III
Contributor III

SET and LET and parameters

Hi,

I can make to easy testing reloads in develop application. So I have to restrict count of records.

Now we set in all SELECT "TOP..." or "WHERE Date <, >, BETWEEN..." and comment and uncomment it... we are doing mistake and it's time-expansive.

I can to have one variable and on this I can use restriction (testing mode) or no (really reload).

SET QV_ModeReally = 1;  // Full reload

SET QV_ModeTest_TOP = 2;  // TEST with TOP restrictions

SET QV_ModeTest_WHERE   = 3;  // TEST with WHERE restrictions

SET QV_Mode=<x>;

So, if I can use TOP, it's OK - I can use one formule for all tables:

SET QV_ClausTop =  " TOP 1000 ";

SET QV_Mode=2;

LET QV_TOP = IF($(QV_Mode)=$(QV_ModeTest_TOP), '$(QV_ClausTop)');

.

.

.

Table1:

LOAD Cis,

    Descr,

    Txt,

    Typ,

    Zkr1;

SQL SELECT $(QV_TOP)

    Cis,

    Descr,

    Txt,

    Typ,

    Zkr1

FROM $(AKTDB).dbo.SPESTR  WHERE Typ = $(SPS_Typ_Kod_Zakazky);

But if I can use condition into WHERE (it's often mode), I have to have many many conditions - for each strictly name of field in SELECT one special condition:

SET QV_ClWhere_DatP =  " (DatP BETWEEN convert( datetime, '2012-09-01', 120) AND convert( datetime, '2012-09-30', 120)) ";

SET QV_ClWhere_Date =  " (Date BETWEEN convert( datetime, '2012-09-01', 120) AND convert( datetime, '2012-09-30', 120)) ";

SET QV_ClWhere_DVyst =  " (DVyst BETWEEN convert( datetime, '2012-09-01', 120) AND convert( datetime, '2012-09-30', 120)) ";


SET QV_Mode=3;

LET QV_WHERE_DATP = IF($(QV_Mode)=$(QV_ModeTest_WHERE), '$(QV_ClWhere_DatP)', ' 1=1 ');

LET QV_WHERE_DATE = IF($(QV_Mode)=$(QV_ModeTest_WHERE), '$(QV_ClWhere_Date)', ' 1=1 ');

LET QV_WHERE_DVYST = IF($(QV_Mode)=$(QV_ModeTest_WHERE), '$(QV_ClWhere_DVyst)', ' 1=1 ');

.

.

TAB1:

LOAD

.

.

SQL SELECT $(QV_TOP)

.

.

FROM $(AKTDB).dbo.FAKTVYDA WITH (NOLOCK)

WHERE

$(QV_WHERE_DatP) AND

. <other conditions>

.

;

TAB2:

LOAD

.

.

SQL SELECT $(QV_TOP)

.

.

FROM $(AKTDB).dbo.FAKTVYDA WITH (NOLOCK)

WHERE

$(QV_WHERE_DVyst) AND

. <other conditions>

.

;

I ask for possibilities use some construction LET / SET / $1 / " / ' ... - call main condition for datetime type with parameter:

?SET? QV_ClWhere_Date =  " ($1 BETWEEN convert( datetime, '2012-09-01', 120) AND convert( datetime, '2012-09-30', 120)) ";

?LET? QV_WHERE_DATE = IF($(QV_Mode)=$(QV_ModeTest_WHERE), '$(QV_ClWhere_Date($1))', ' 1=1 ');

TAB1:

LOAD

.

.

SQL SELECT $(QV_TOP)

.

.

FROM $(AKTDB).dbo.FAKTVYDA WITH (NOLOCK)

WHERE

$(QV_WHERE_DATE(?DatP?)) AND

. <other conditions>

.

;

Please, is some way for this?

Many thanks,

Olda

0 Replies