How to sum or count a value from not joined table?

[pablosilvestro]

Hi all,

my question is how to make something like a Subquery and save the  value in a field from a resident table

for example:

Table:

load * inline

[Date, Value

'01/01/2017' , 5

'02/01/2017' , 20

'03/01/2017' , 4

'01/02/2017', 10

'05/02/2017' ,1];

Data:

load * inline

[Month , from, to

'Jan', '01/01/2017' , '31/01/2017'

'Feb' , '01/02/2017' , '28/02/2017' ]

I want to create a new table with sumarized values from "Table" for each month.

how can I reach this in qlikview?

thanks in advance.

[trdandamudi]

One way is as below:

Table:
load * inline
[Date, Value
'01/01/2017' , 5
'02/01/2017' , 20
'03/01/2017' , 4
'01/02/2017', 10
'05/02/2017' ,1];


Data:
  load * inline
[Month , from, to
'Jan', '01/01/2017' , '31/01/2017'
'Feb' , '01/02/2017' , '28/02/2017' ];


Final_Table:
NoConcatenate
Load
Month(Date) as Month,
Sum(Value) as Total_Value
Resident Table
Group By Month(Date);

Drop Table Table;

[pablosilvestro]

Nice trick,

But actually will not helpme, because in my real scenario, I need to count or sum some important or flaged days betweens these dates.

that is why I need to create something like a subquery or lookup function which reads table and return a value based on From-to conditions.

[sergio0592]

And why you don't apply a restriction on these flaged days with :

Load
Month(Date) as Month,
Sum(Value) as Total_Value
Resident Table

where Flag_days=1
Group By Month(Date);

[MayilVahanan]

HI

Try with IntervalMatch concept in Qlik. It will help to resolve your problem.

intevalMatch.pdf

http://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/ScriptPrefixes/...

[pablosilvestro]

after several tries, we used a crossjoin and count values with criteria covering all the possibles matchs.

thanks in advance