join with Cartesian product (join con prodotto cartesiano)

[captain89]

Hi!, I need the result of this sql join in a qlik table:

with tab1 as (                                          

select cdarmm, cdmgmm, min(dtmomm) as dtmin, max(dtmomm) as dtmax

from mgmov00f group by cdarmm, cdmgmm)                                

select cdarmm, cdmgmm, dtmomx from tab1 join tab2 on dtmomx > dtmin and dtmomx<=dtmax

I've loaded two tables in my .qvw:

tab1:

Item Code	Inventory	Date first mov	Date last mov
A001	888	20120103	20120301
A001	001	20110102	20110430
B002	555	20140608	20140701
B002	001	20140501	20140920

tab2:

Monthly Calendar
20120101
20120201
20120301
20120401
20120501
.........
20140801
20140901

This is the result I need:

Tab3:

Item Code	Inventory	Monthly
A001	888	20120101
A001	888	20120201
A001	888	20120301
A001	001	20110101
......	......	.....

I must select the record where the (date first mov < monthly calendar) and (date last mov. >= monthly calendar)

So I need to duplicate records like a cartesian product but conditioned by an interval of date.

Sorry for my English.

Thanks for the support.

[arthur_dom]

tried intervalmatch function?

Re: IntervalMatch Multiplying Records

[maxgro · Accepted solution]

a:

LOAD [Item Code],

     Inventory,

     [Date first mov],

     [Date last mov]

FROM

[https://community.qlik.com/thread/167817]

(html, codepage is 1252, embedded labels, table is @1);

for y = 2011 to 2014

  for m=1 to 12

  b:

  load $(y) & num($(m), '00') & '01' as [Monthly Calendar]

  autogenerate 1;

next;

next;

join(a) load * Resident b;

DROP Table b;

final:

NoConcatenate

load * Resident a

where left([Monthly Calendar],6) >= left([Date first mov],6) and left([Monthly Calendar],6) <= left([Date last mov],6);

DROP Table a;

[captain89]

Thanks Massimo. Maybe I did it in the same way you indicated, I think.

First I make three variables:

LET vFIRST = LEFT(FieldValue('V_DTMINN', 1), 6); (this is the date of the first movement in the inventory file)

LET vSTART = 20140101;

LET vOGGI = year(today())*10000+month(today())*100+day(today());

Then I load a table with the date of the first and the last stock movement for item number, inventory number

[Data Primo-Ultimo Mov.]:

LOAD

TEXT(CDARMM) AS [Cod. Articolo W],

Text(CDMGMM) as [Cod. Magazzino W],

Date(Date#(DTMMIN, 'YYYYMMDD')) AS [Data Primo Mov.Mag. W],

Date(Date#(DTMMAX, 'YYYYMMDD')) as [Data Ultimo Mov. Mag W],

Date(Date#(left($(vOGGI), 6)*100+1, 'YYYYMMDD')) as [Data Ultimo Saldo W],

date(date#(if((LEFT(DTMMIN, 6)*100+1)>=$(vSTART),  LEFT(DTMMIN, 6)*100+1, $(vSTART)), 'YYYYMMDD'))  AS [Data Primo Saldo W]

;

SQL

SELECT CDARMM, CDMGMM, MAX(DTMOMM) AS DTMMAX, MIN(DTMOMM) as DTMMIN FROM MGMOV00F

WHERE CDARMM IN $(vIN)

GROUP BY CDARMM, CDMGMM

;

I joined with the monthly calendar:

join

load

date(Date#([Data Saldo Work]*100+1, 'YYYYMMDD')) as [Data Saldo W]

;

load

if((left($(vFIRST),4)=left(recno()+$(vFIRST)-1,4) and right(recno()+$(vFIRST)-1,2)*1<13), recno()+$(vFIRST)-1, recno()+$(vFIRST)-1+

88*(floor((right(recno()+$(vFIRST)-1,3)*1-(mid($(vFIRST),4,1)*100+12)-1)/12)+1)) as [Data Saldo Work]

Autogenerate(left($(vOGGI), 6)-left($(vFIRST), 6) - 88*(left($(vOGGI), 4)-left($(vFIRST), 4)) + 1);

I take only the records between the interval of dates.

[Saldi]:

load

text([Cod. Articolo W]) as [Cod. Articolo W1],

text([Cod. Magazzino W]) as [Cod. Magazzino W1],

[Data Saldo W] as [Data Saldo W1],

[Data Primo Saldo W] as [Data Primo Saldo W1],

[Flag Ripresa Saldi W] as [Flag Ripresa Saldi W1]

resident [Data Primo-Ultimo Mov.]

where [Data Saldo W]>=[Data Primo Saldo W]

;

drop table [Data Primo-Ultimo Mov.];

left join ([Saldi])

LOAD

text(CDARMM) AS [Cod. Articolo W1],

text(CDMGMM) AS [Cod. Magazzino W1],

date(Date#(DTMOMM*100+1, 'YYYYMMDD')) AS [Data Mese Variazione W1],

QTMOMS AS [Qtà Variaz. Mensile W1]

;

SQL: sum of the stock movements by item number, inventory number, monthname

.......

I do the inventory balances:

[Saldi Finale]:

load

[Cod. Articolo W1] as [Cod. Articolo W2],

[Cod. Magazzino W1] as [Cod. Magazzino W2],

[Data Saldo W1] as [Data Saldo W2],

sum([Qtà Variaz. Mensile W1]) as [Qtà Saldo Mensile W2]

resident Saldi

where [Data Saldo W1]>[Data Mese Variazione W1]

GROUP BY [Cod. Articolo W1], [Cod. Magazzino W1], [Data Saldo W1]

;

drop table Saldi;

then i re-load all the stock movements... and with a concatenate I consider the montly inventory balance like a simple stock movement.