topic Re: How to sum or count a value from not joined table? in QlikView

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

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

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.

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

trdandamudi — Tue, 02 Jan 2018 21:27:26 GMT

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;

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

pablosilvestro — Tue, 02 Jan 2018 21:34:16 GMT

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.

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

sergio0592 — Tue, 02 Jan 2018 21:55:32 GMT

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

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

MayilVahanan — Wed, 03 Jan 2018 06:18:25 GMT

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

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

pablosilvestro — Tue, 16 Jan 2018 18:31:29 GMT

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

thanks in advance