Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
sindus123
New Contributor III

Help on button creation

Hi All,

Please help me on below requirement.

1. I need to Create two buttons  i.e StatusOk and Status NotOk .

StatusOk button which displays the count of status for selected continent from attached sample.

Status NotOk button should display the  count of missing records for the selected continent  based on  the date.


2.On the selection of StatusOk button it should  navigate to the detail table which should display the respective records for the selected continent. and when we  click on Status NotOk button it should  display the missing records(i.e for Continent Asia missing record is 1 on 20170302)



For continent Asia the  StatusOk is 11 and  Status NotOk is 1( because for date 20170302 segment A the region south is missing  )

for continent Europe the StatusOk is 12 and  Status NotOk  is 0

 

ContinentSegmentRegionDateStatus
AsiaANorth201703011
AsiaASouth201703011
AsiaBNorth201703011
AsiaB South201703011
AsiaCNorth 201703011
AsiaCSouth201703011
EuropeANorth201703011
EuropeASouth201703011
EuropeBNorth201703011
EuropeB South201703011
EuropeCNorth 201703011
EuropeCSouth201703011
AsiaANorth201703021
AsiaBNorth201703021
AsiaB South201703021
AsiaCNorth 201703021
AsiaCSouth201703021
EuropeANorth201703021
EuropeASouth201703021
EuropeB North201703021
EuropeB South201703021
EuropeCNorth 201703021
EuropeCSouth201703021

Thanks in advance

1 Solution

Accepted Solutions

Re: Help on button creation

first you need to generate the missing Dates for each combination of Continent, Region & Segment. Below is the script for this

Data:

LOAD Continent,

    Segment,

    Region,

    Continent&Segment&Region&date(date#(Date,'YYYYMMDD')) as Key1,

    date(date#(Date,'YYYYMMDD')) as Date,

    Status

FROM

[Data.xlsx]

(ooxml, embedded labels, table is Sheet1);

MinMax:

LOAD

    min(Date) as MinDate,

    max(Date) as MaxDate;

LOAD FieldValue('Date',RecNo()) as Date

AutoGenerate FieldValueCount('Date');

let vMinDate = Peek('MinDate',0,'MinMax');

let vMaxDate = Peek('MaxDate',0,'MinMax');

Dates:

LOAD date($(vMinDate) +IterNo()-1) as AllDate

AutoGenerate 1

While $(vMinDate) +IterNo()-1 <= $(vMaxDate);

Combination:

LOAD Continent,

    Segment,

    Region

Resident Data;

Left Join(Combination)

LOAD AllDate

Resident Dates;

DROP Table Dates;

Allkey:

LOAD *,

    Continent&Segment&Region&AllDate as Key2

Resident Combination;

DROP Table Combination;

Concatenate(Data)

LOAD *

Resident Allkey

where not Exists(Key1,Key2);

DROP Tables Allkey;

DROP Fields Key2,Key1,AllDate;



Capture1.JPG


Capture.JPG

View solution in original post

6 Replies
beck_bakytbek
Honored Contributor

Re: Help on button creation

sindus123
New Contributor III

Re: Help on button creation

Please help me with the below requirement.

1. I need to Create two buttons  i.e StatusOk and Status NotOk .

StatusOk button which displays the count of status for selected continent from attached sample.

Status NotOk button should display the  count of missing records for the selected continent  based on  the date.


2.On the selection of StatusOk button it should  navigate to the detail table which should display the respective records for the selected continent. and when we  click on Status NotOk button it should  display the missing records(i.e for Continent Asia missing record is 1 on 20170302)



For continent Asia the  StatusOk is 11 and  Status NotOk is 1( because for date 20170302 segment A the region south is missing  )

for continent Europe the StatusOk is 12 and  Status NotOk  is 0

ContinentSegmentRegionDateStatus
AsiaANorth201703011
AsiaASouth201703011
AsiaBNorth201703011
AsiaBSouth201703011
AsiaCNorth201703011
AsiaCSouth201703011
EuropeANorth201703011
EuropeASouth201703011
EuropeBNorth201703011
EuropeBSouth201703011
EuropeCNorth201703011
EuropeCSouth201703011
AsiaANorth201703021
AsiaBNorth201703021
AsiaBSouth201703021
AsiaCNorth201703021
AsiaCSouth201703021
EuropeANorth201703021
EuropeASouth201703021
EuropeBNorth201703021
EuropeBSouth201703021
EuropeCNorth201703021
EuropeCSouth201703021
parul_mehta
Contributor

Re: Help on button creation

Kavyashree,

for req 1)

If you need the count of status based on the current selected continent, you can add this variable, and then use the same variable to get the count in a text box

Will check and get back on how the button can be used for the same.

Re: Help on button creation

Don't create double threads for single Issue. Have a look Re: Missing Count Details

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

Re: Help on button creation

first you need to generate the missing Dates for each combination of Continent, Region & Segment. Below is the script for this

Data:

LOAD Continent,

    Segment,

    Region,

    Continent&Segment&Region&date(date#(Date,'YYYYMMDD')) as Key1,

    date(date#(Date,'YYYYMMDD')) as Date,

    Status

FROM

[Data.xlsx]

(ooxml, embedded labels, table is Sheet1);

MinMax:

LOAD

    min(Date) as MinDate,

    max(Date) as MaxDate;

LOAD FieldValue('Date',RecNo()) as Date

AutoGenerate FieldValueCount('Date');

let vMinDate = Peek('MinDate',0,'MinMax');

let vMaxDate = Peek('MaxDate',0,'MinMax');

Dates:

LOAD date($(vMinDate) +IterNo()-1) as AllDate

AutoGenerate 1

While $(vMinDate) +IterNo()-1 <= $(vMaxDate);

Combination:

LOAD Continent,

    Segment,

    Region

Resident Data;

Left Join(Combination)

LOAD AllDate

Resident Dates;

DROP Table Dates;

Allkey:

LOAD *,

    Continent&Segment&Region&AllDate as Key2

Resident Combination;

DROP Table Combination;

Concatenate(Data)

LOAD *

Resident Allkey

where not Exists(Key1,Key2);

DROP Tables Allkey;

DROP Fields Key2,Key1,AllDate;



Capture1.JPG


Capture.JPG

View solution in original post

sindus123
New Contributor III

Re: Help on button creation

Thanks eveyone for replies:)