Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

How to Limit values from Dimension using List Boxes selection?

    I have Year, Month, Day as List Boxes selection. And in the pivot table, I have Shop as dimension. Now, every shop has ClosingDate and OpeningDate. If shop has no ClosingDate, then it has NULL in it, which means shop is Open.


    Now here, I want to show only those shops in the pivot table, which doesn't have OpeningDate and ClosingDate in the List Boxes selection period. Which means that shop was neither opened nor closed in that List Boxes selection Period.

   Anyone with the scipt for me here? Thanks.

1 Solution

Accepted Solutions
MVP
MVP

Re: How to Limit values from Dimension using List Boxes selection?

Hi,

Try this in script

LOAD

*,

If(Len(Trim(ClosedDate)) > 0 OR  Len(Trim(ClosedDate)) > 0, 1, 0) AS Flag

FROM DataSource;

Now in pivot table use this expression.

=Only({<Flag = {0}>} Shop)

Regards,

Jagan.


3 Replies
MVP
MVP

Re: How to Limit values from Dimension using List Boxes selection?

Hi,

Try this in script

LOAD

*,

If(Len(Trim(ClosedDate)) > 0 OR  Len(Trim(ClosedDate)) > 0, 1, 0) AS Flag

FROM DataSource;

Now in pivot table use this expression.

=Only({<Flag = {0}>} Shop)

Regards,

Jagan.


Not applicable

Re: How to Limit values from Dimension using List Boxes selection?

But here, I have the data in form of view from MS SQL Server. Can you convert it into expression? So i may use it in pivot table as Calculated Dimension. Many thanks.

MVP
MVP

Re: How to Limit values from Dimension using List Boxes selection?

Hi,

It is very much easier to achieve this in script, also it is efficient.  Try this script

Data:

LOAD

*,

If(Len(Trim(ClosedDate)) > 0 OR  Len(Trim(ClosedDate)) > 0, 1, 0) AS Flag

FROM DataSource;

SQL SELECT

*

FROM SQL_VIEW_NAME;

Regards,

Jagan.

Community Browser