topic Re: How to store Data into a table box from two sources in one table in QlikView

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

phb_nostromo — Wed, 25 Nov 2020 16:16:04 GMT

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

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

Chanty4u — Wed, 14 Mar 2018 12:25:29 GMT

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

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

phb_nostromo — Wed, 14 Mar 2018 12:27:56 GMT

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

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

tamilarasu — Wed, 14 Mar 2018 12:42:54 GMT

Hi Philippe,

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

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

sergeyay — Wed, 14 Mar 2018 12:44:35 GMT

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

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

phb_nostromo — Wed, 14 Mar 2018 12:49:45 GMT

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 ..

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

phb_nostromo — Wed, 14 Mar 2018 13:06:46 GMT

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 !

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

phb_nostromo — Wed, 14 Mar 2018 13:07:44 GMT

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

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

tamilarasu — Wed, 14 Mar 2018 13:12:44 GMT

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

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

phb_nostromo — Wed, 14 Mar 2018 13:43:19 GMT

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

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

tamilarasu — Wed, 14 Mar 2018 14:05:08 GMT

I am clear on that now . So you need only week numbers and the corresponding year i.e 54 (2017) + 54 (2017) = 208 weeks in this case. The first code will produce duplicate year week numbers. So the second script is the one you are looking for. Have a super day, Philippe!