Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Two where clauses

Hi

I'm wondering what the best/Fastest way to create a table with "two" where-clauses would be.

This is how I have thought doing it

RawData:
LOAD
Field1,
Field2,
Field3,
Field4,
Date
from someqvdfile;

Dates:
load Date resident RawData;

Let a = NoOfRows('Dates')
FOR I=0 to a
Let DateNow=fieldvalue('Date',I)

Table1:
LOAD
Field1,
Field2,
'0' as Field3,
sum(Field4),
$(DateNow) as TheDate
resident RawData where Date<=$(DateNow);

ADD LOAD
Field1,
Field2,
sum(Field3),
'0' as Field4
$(DateNow) as TheDate
resident RawData where Date=$(DateNow);

... and then clear all entries where both Field3=0 and Field4=0 etc...

See what I'm trying to do here? In Table1 I want Field4 to be summed for all dates up to $(DateNow) and this works (verified) and then to this table I want to add the sum of Field3 for ONLY the date on $(DateNow). My solution seems correct in my eyes but doesnt seem to work. What am I doing wrong? Field3 becomes 0 on all entries

Thankful for help!

Tags (1)
2 Replies
pover
Honored Contributor

Two where clauses

In the row with sum(Field3), you have to define it alias or sum(Field3) as Field3.

ADD is for partial loads. Maybe you are doing a partial load, but you might be looking to concatenate the tables. It that's the case you should use the function CONCATENATE.

If you have a sum in the script, the other fields in the load have to be included in a group by:

Load Field1,
Field2,
sum(Field3) as Field3
Resident Table
Group by Field1,Field2;

That's all I can see for now. I hope that helps.

Regards.

Regards.

Not applicable

Two where clauses

Ok, Thanks, I suspected that CONCATENATE might help me but I don't quite know how it works? Could anyone give an example using my table(s) above?

Thanks!

Community Browser