topic Re: LOADING SELECTED VALUES in App Development

LOADING SELECTED VALUES

philgood34 — Sun, 15 Oct 2017 06:06:04 GMT

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

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

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

Re: LOADING SELECTED VALUES

antoniotiman — Sun, 15 Oct 2017 07:11:20 GMT

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;

Re: LOADING SELECTED VALUES

philgood34 — Sun, 15 Oct 2017 07:32:44 GMT

hi Antonio

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

does it change your answer ?

Re: LOADING SELECTED VALUES

antoniotiman — Sun, 15 Oct 2017 07:57:38 GMT

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;

Re: LOADING SELECTED VALUES

philgood34 — Sun, 15 Oct 2017 07:58:05 GMT

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

Re: LOADING SELECTED VALUES

antoniotiman — Sun, 15 Oct 2017 08:04:36 GMT

Philippe,

this is my sample data (Inline files)

You change to

LOAD *

From YourTable.;

If You want then provide Your Files.

Re: LOADING SELECTED VALUES

philgood34 — Sun, 15 Oct 2017 08:20:07 GMT

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

Re: LOADING SELECTED VALUES

antoniotiman — Sun, 15 Oct 2017 09:06:11 GMT

[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;

Re: LOADING SELECTED VALUES

philgood34 — Sun, 15 Oct 2017 09:21:37 GMT

Thank's a lot

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

Re: LOADING SELECTED VALUES

antoniotiman — Sun, 15 Oct 2017 09:36:53 GMT

Re: LOADING SELECTED VALUES

philgood34 — Sun, 15 Oct 2017 09:51:24 GMT

So what ?

The goal is to load CAT B from March 2017 to FEBRUARY 2018

on March 2017 i don't expect to find CAT A but only CAT B as well as April , May .... 2017

Re: LOADING SELECTED VALUES

antoniotiman — Sun, 15 Oct 2017 10:07:07 GMT

Remove +1 from FINISH+1

[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) as End
FROM [lib://LOAD_SIM/EXAMPLE 2 BIS.xlsx]
(ooxml, embedded labels, table is S)
Where S_SELECT = 1;

Re: LOADING SELECTED VALUES

philgood34 — Sun, 15 Oct 2017 10:34:24 GMT

Great job Antonio !

So let me well understand

first i load the datas tab with

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);

then, i select only the CAT i want to be loaded in my datas tab using ma secondary tab with

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

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

(ooxml, embedded labels, table is S)

Where S_SELECT = 1;

Finally, the secondary is not loaded , just use to select

Right ?

Re: LOADING SELECTED VALUES

antoniotiman — Sun, 15 Oct 2017 10:47:10 GMT

"Where S_SELECT = 1" set to only Row affected

Inner Join reduce table DATAS to Expanded Rows ob 2nd table.

Re: LOADING SELECTED VALUES

philgood34 — Sun, 15 Oct 2017 10:50:41 GMT

Got it

Last question to resolve my other issue by myself :

If i have an other tab to load like 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);

DATAS2]:

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

[G_YEAR],

[G_MONTH],

[G_CATEGORIES],

[G_GOAL_SALES]

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

(ooxml, embedded labels, table is DATAS2);

If i want to apply the same selection in my 2 tables like before, what is the good syntax ?

Some thing like this ?

Inner Join

LOAD DISTINCT S_CATEGORIES as D_CATEGORIES, S_CATEGORIES as G_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;

Or repeat the inner Join after each tab ?

Re: LOADING SELECTED VALUES

antoniotiman — Sun, 15 Oct 2017 18:09:38 GMT

Concatenate two tables by changing Fields name, like G_YEAR as D_YEAR,G_MONTH as D_MONTH,....,

then Inner Join

Re: LOADING SELECTED VALUES

philgood34 — Mon, 16 Oct 2017 06:23:24 GMT

Hi Antonio

I tried

[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);

DATAS2]:

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

[G_YEAR] As [D_YEAR],

[G_MONTH] As [D_MONTH],

[G_CATEGORIES] As [D_CATEGORIES],

[G_GOAL_SALES]

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

(ooxml, embedded labels, table is DATAS2);

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

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

(ooxml, embedded labels, table is S)

Where S_SELECT = 1;

But i got

apart from that

is it compatible with LOAD (My date(MakeDate([D_YEAR],…. ?

regards

Philippe

Re: LOADING SELECTED VALUES

antoniotiman — Mon, 16 Oct 2017 07:09:37 GMT

Yes, You don't have D_YEAR in "EXEMPLE 2 BIS".

I'm sorry, I don't understand Your new request.

Can You provide Your Tables, and expected result ?

Re: LOADING SELECTED VALUES

philgood34 — Mon, 16 Oct 2017 16:13:58 GMT

But i just copied your script and completed with DATAS2??

[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);

[DATAS2]:

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

[G_YEAR] As [D_YEAR],

[G_MONTH] As [D_MONTH],

[G_CATEGORIES] As [D_CATEGORIES],

[G_GOAL_SALES]

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

(ooxml, embedded labels, table is DATAS2);

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

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

(ooxml, embedded labels, table is S)

Where S_SELECT = 1;

Can you help me please ?

Re: LOADING SELECTED VALUES

antoniotiman — Mon, 16 Oct 2017 16:41:42 GMT

This is Your above script

DATAS2]:

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

[G_YEAR] As [D_YEAR],

[G_MONTH] As [D_MONTH],

[G_CATEGORIES] As [D_CATEGORIES],

[G_GOAL_SALES]

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

(ooxml, embedded labels, table is DATAS2);

Please, provide Your Data Sample (Table) and Your expected result.

Regards,

Antonio