Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

philgood34
Contributor II

LOADING SELECTED VALUES

hi everybody

i have 2 tabs

the first with datas (e.g. SALES  ) and dates as Month & Year

You'll see there are 2 specificities :

- mobile years March to February

- 2 categories of data existing or not depending on the mobile years

DATAS_CAT.png



The second tabs give the selected categorie (only one per mobile year to use in my app)

DATA_SELECT.png



My issue


i want to have a script that load only the selected categorie in the datas tab ?


in my example i want to load only datas from

category A  03/2016 to 02/2017   

category B 03/2017 to 02/2018  


I began by this script,



[DATAS]:

LOAD Date(MakeDate([D_YEAR], [D_MONTH]), 'MM_YYYY') as MonthYear,

                [D_YEAR],

                [D_MONTH],

    [D_CATEGORIES],

                [SALES]

FROM [lib://LOAD_SIM/EXAMPLE 2.xlsx]

(ooxml, embedded labels, table is DATAS);

:

LOAD [S_CATEGORIES],

                [S_YEAR],

                [BEGIN],

                [FINISH],

                [S_SELECT]

FROM [lib://LOAD_SIM/EXAMPLE 2 BIS.xlsx]

(ooxml, embedded labels, table is S);

by i don't know the expression to make the job ....

Thank's by advance

Philippe

1 Solution

Accepted Solutions
antoniotiman
Honored Contributor III

Re: LOADING SELECTED VALUES

Try this

[DATAS]:
LOAD Date(MakeDate([D_YEAR], [D_MONTH]), 'MM_YYYY') as MonthYear,
[D_YEAR],
[D_MONTH],
[D_CATEGORIES],
[SALES]
FROM [lib://LOAD_SIM/EXAMPLE 2.xlsx]
(ooxml, embedded labels, table is DATAS);
Inner Join (DATAS)
LOAD DISTINCT S_CATEGORIES as D_CATEGORIES,Year(Start+IterNo()-1) as D_YEAR,Num(Month(Start+IterNo()-1)) as D_MONTH
While Start+IterNo()-1 <= End;
LOAD *,MakeDate(S_YEAR,BEGIN) as Start,MakeDate(S_YEAR+1,FINISH) as End
FROM [lib://LOAD_SIM/EXAMPLE 2 BIS.xlsx]
(ooxml, embedded labels, table is S)
Where S_SELECT = 1;

[DATAS2]:
LOAD Date(MakeDate([G_YEAR], [G_MONTH]), 'MM_YYYY') as MonthYear,
[G_YEAR],
[G_MONTH],
[G_CATEGORIES],
[G_GOAL_SALES]
FROM [lib://LOAD_SIM/EXAMPLE 3.xlsx]
(ooxml, embedded labels, table is DATAS2);
Inner Join (DATAS2)
LOAD DISTINCT S_CATEGORIES as G_CATEGORIES,Year(Start+IterNo()-1) as G_YEAR,Num(Month(Start+IterNo()-1)) as G_MONTH
While Start+IterNo()-1 <= End;
LOAD *,MakeDate(S_YEAR,BEGIN) as Start,MakeDate(S_YEAR+1,FINISH) as End
FROM [lib://LOAD_SIM/EXAMPLE 2 BIS.xlsx]
(ooxml, embedded labels, table is
S)
Where S_SELECT = 1;

27 Replies
antoniotiman
Honored Contributor III

Re: LOADING SELECTED VALUES

May be this

LOAD * Inline [
D_CATEGORIES,D_YEAR,D_MONTH,SALES
A,2016,2,1000
A,2016,3,2000
A,2017,2,2000
A,2017,5,2000
B,2017,3,1500]
;

Inner Join
LOAD DISTINCT S_CATEGORIES as D_CATEGORIES,Year(Start+IterNo()-1) as D_YEAR,Num(Month(Start+IterNo()-1)) as D_MONTH
While Start+IterNo()-1 <= End;
LOAD *,MakeDate(S_YEAR,BEGIN) as Start,MakeDate(S_YEAR+1,FINISH) as End
Inline [
S_CATEGORIES,S_YEAR,BEGIN,FINISH,S_SELECT
A,2016,3,2,1
A,2017,3,2,
B,2017,3,2,1]

Where S_SELECT = 1;

philgood34
Contributor II

Re: LOADING SELECTED VALUES

hi Antonio

first, I have to fix an error in my first table (sorry for that) :yellow cells

CORRECT.png

does it change your answer ?

antoniotiman
Honored Contributor III

Re: LOADING SELECTED VALUES

LOAD * Inline [
D_CATEGORIES,D_YEAR,D_MONTH,SALES
A,2016,2,1000
A,2016,3,2000
A,2017,2,2000
A,2017,3,4000
A,2017,5,2000
B,2017,3,1500]
;

Inner Join
LOAD DISTINCT S_CATEGORIES as D_CATEGORIES,Year(Start+IterNo()-1) as D_YEAR,Num(Month(Start+IterNo()-1)) as D_MONTH
While Start+IterNo()-1 <= End;
LOAD *,MakeDate(S_YEAR,BEGIN) as Start,MakeDate(S_YEAR+1,FINISH+1) as End
Inline [
S_CATEGORIES,S_YEAR,BEGIN,FINISH,S_SELECT
A,2016,3,2,1
A,2017,3,2,
B,2017,3,2,1]

Where S_SELECT
= 1;

philgood34
Contributor II

Re: LOADING SELECTED VALUES

if not.

Should i understand i have to write all these lines for every year in the future ?

(LOAD * Inline [
D_CATEGORIES,D_YEAR,D_MONTH,SALES
A,2016,2,1000
A,2016,3,2000
A,2017,2,2000
A,2017,5,2000
B,2017,3,1500]

I took a simplified example, but in my real tabs, i have many years and catégories ...variable according to the users

so i need a script that makes the job without re-write the datas ... But may be i didn't understood the meaning of the serie of numbers 1000, 2000 ...

If it is necessary, i can add a column to concatenate catégories and year (as shown above) .. may be easier to script ? need to eliminate rows where A2017 is present and Keep A2016 & B2017 ?

regards

Philippe

antoniotiman
Honored Contributor III

Re: LOADING SELECTED VALUES

Philippe,

this is my sample data (Inline files)

You change to

LOAD *

From YourTable.;

If You want then provide Your Files.

philgood34
Contributor II

Re: LOADING SELECTED VALUES

Very nice

i join my file LOADING SIM 2

if it's working can you take a look at another thread (without answer) a little more complex

SELECTING FORECAST IN LOADING SCRIPT

(the same issue but with fiscal year already in the script ... : LOADING SIM)

Thank's a lot

Philippe

antoniotiman
Honored Contributor III

Re: LOADING SELECTED VALUES

[DATAS]:
LOAD Date(MakeDate([D_YEAR], [D_MONTH]), 'MM_YYYY') as MonthYear,
[D_YEAR],
[D_MONTH],
[D_CATEGORIES],
[SALES]
FROM [lib://LOAD_SIM/EXAMPLE 2.xlsx]
(ooxml, embedded labels, table is DATAS);

Inner Join
LOAD DISTINCT S_CATEGORIES as D_CATEGORIES,Year(Start+IterNo()-1) as D_YEAR,Num(Month(Start+IterNo()-1)) as D_MONTH
While Start+IterNo()-1 <= End;
LOAD *,MakeDate(S_YEAR,BEGIN) as Start,MakeDate(S_YEAR+1,FINISH+1) as End
FROM [lib://LOAD_SIM/EXAMPLE 2 BIS.xlsx]
(ooxml, embedded labels, table is S)
Where S_SELECT = 1;

philgood34
Contributor II

Re: LOADING SELECTED VALUES

Thank's a lot

it's seem almost perfect .. except for March 2017 ... i have still the 2 categories ...

STILL A.png

antoniotiman
Honored Contributor III

Re: LOADING SELECTED VALUES

Community Browser