Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pablosilvestro
Contributor II
Contributor II

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

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.

5 Replies
trdandamudi
Master II
Master II

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
Contributor II
Contributor II
Author

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
Specialist III
Specialist III

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

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
pablosilvestro
Contributor II
Contributor II
Author

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

thanks in advance