How to store Data into a table box from two sources in one table

[phb_nostromo]

That code : ( inspired from Qlik Community thanks ) :

LET vStartDate = MakeDate(2017, 1, 1);
LET vEndDate = MakeDate(num(year(Today())), 12, 31); 
LET vNumberOfDays = vEndDate - vStartDate + 1; 

TableCroisee:
load ADV from [$(vs_Qvd)BaseSHP_ADV0.qvd] (qvd);
load
year(Date(makedate(2017)+recno()-1))*100 + week(Date(makedate(2017)+recno()-1)) as AnSem
AutoGenerate $(vNumberOfDays);

Gives me that table :

Under two sources :

What shoud i add to be able to store the result in only one table ?

Thanks a million

[Chanty4u]

Hi,

select one table first add all the fields

then select second table from dropdown and add all of them it will show the two tables data na

[phb_nostromo]

Hi.. sorry but i don't understand what you mean

[tamilarasu]

Hi Philippe,

Could you explain us what is your exact requirement and expected output.

[sergeyay]

Hello,

How many values in ADV? I try to understand, what result do you need.

If ADV has 3 values, you will get a table with 3*730 = 2190 lines. Right?

Regards,

Serge

[phb_nostromo]

Hi guys, thanks to follow,

ADV is a list of names, and for each name i want to build a table with all different values of AnSem..

In fact, what i expect as an output is only one name of table ( ie  Table croisee, but not two tables Table croisee and autogenerate(730) to be able to store it and go ahead.  Just an output equivalent to what i would get if i exported the result in excel .

Hope it's clear ..

[phb_nostromo · Accepted solution]

Thanks Tamil .. i did'nt think to introduce this outerjoin there .

Thanks a lot Tamil, and everyone on this helpful forum

But something went wrong as your answer is not there anymore, but hopefully i got it !

[phb_nostromo]

Right code from Tamil :

LET vStartDate = MakeDate(2017, 1, 1);
LET vEndDate = MakeDate(num(year(Today())), 12, 31); 
LET vNumberOfDays = vEndDate - vStartDate + 1; 

TableCroisee:
load ADV
From
[$(vs_Qvd)BaseSHP_ADV0.qvd] (qvd);

Outer Join (TableCroisee)
load
year(Date('$(vStartDate)'+recno()-1))*100 + week(Date('$(vStartDate)'+recno()-1)) as AnSem
AutoGenerate $(vNumberOfDays); 

[tamilarasu]

Ha sorry, Philippe. I deleted my reply by mistake. I have a quick question. Do you want to create week lines for different names. Right? So 52 weeks + 52 weeks = 104 weeks. I believe your script needs some modification. Could you explain (expected result) the date part script, please?

I believe this is what you want?!?!

LET vStartDate   = MakeDate(2017);

LET vEndDate     = YearEnd(Today()); 
LET vNumberOfDays = vEndDate - vStartDate + 1; 

TableCroisee:
load ADV
From
[$(vs_Qvd)BaseSHP_ADV0.qvd] (qvd);

Outer Join (TableCroisee)
load Distinct
Year('$(vStartDate)'+recno()-1)*100 + Week('$(vStartDate)'+recno()-1) as AnSem
AutoGenerate $(vNumberOfDays);

[phb_nostromo]

Thanks Tamil. The aim of the script is to gather available time from external data for each agent per week, and add the incoming duties per week, duty that will be repeated eventualy over a few weeks until every duty weight will be released at its time. So i need a blank table and fit data in.

So, after the previous code :

BaseSHP_ADV1:
load *,
Min&Max as MinMax,
ADV & '/' & [Annee Semaine] as ClefOne
from [$(vs_Qvd)BaseSHP_ADV0.qvd] (qvd);
store BaseSHP_ADV1 into [$(vs_Qvd)BaseSHP_ADV1.qvd] (qvd);
drop table BaseSHP_ADV1;

BaseSHP_ADV2:
load ADV,AnSem from [$(vs_Qvd)TableCroisee.qvd] (qvd);
left join
load ADV,[Annee Semaine] as AnSem,MinutesDispo from [$(vs_Qvd)DispoGTATempo3.qvd] (qvd);
left join
load * from [$(vs_Qvd)BaseSHP_ADV1.qvd] (qvd);

Then later i integrate the duty

Result0:
load ADV,
IndexSemainePlancher, ( week start)
IndexSemainePlafond,  ( week end )
ChargeLigne,   ( the duty )
IndexSemainePlancher + IterNo() - 1 as IndexSemaine
Resident Data
while IndexSemainePlancher + IterNo() - 1 <= IndexSemainePlafond;
drop table Data;
store Result0 into [$(vs_Qvd)Result0.qvd] (qvd);
drop table Result0;

Regards, Tamil, and Thanks a lot