LOADING SELECTED VALUES

[philgood34]

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

[philgood34]

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

[antoniotiman]

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;

[philgood34]

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 ?

[antoniotiman]

"Where S_SELECT = 1" set to only Row affected

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

[philgood34]

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 ?

[antoniotiman]

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

then Inner Join

[philgood34]

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

[antoniotiman]

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 ?

[philgood34]

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 ?

[antoniotiman]

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