topic Re: Concatenate fact tables in QlikView

Concatenate fact tables

Fri, 16 Aug 2013 08:06:55 GMT

Hi Community,

I have 2 fact tables that include exactly the same fields and I am trying to join them. Everything seems fine up until I try to calculate a bit more comple expressions

My fact tables are:

nv200_Data:

LOAD TestID,

Date(TestDate) as TestDate,

TestStatus,

if(TestStatus='Pass',1,0) as TestStatusBool,

UnitSerial,

UnitFirmware,

Month(TestDate) as TestMonth,

Year(TestDate) as TestYear,

Week(TestDate)as TestWeek,

Day(TestDate) As TestDay,

'Q' &Ceil(Month(TestDate)/3) as Quarter,

'1' as PassRateCounter,

'NV200' as ValidatorUnit,

'Initialisation' as Phase,

TestID&'-'&'NV200' as SensorValuesKey;

nv200_Data_2:

Concatenate(nv200_Data)

LOAD TestID,

Date(TestDate) as TestDate,

TestStatus,

if(TestStatus='Pass',1,0) as TestStatusBool,

UnitSerial,

UnitFirmware,

Month(TestDate) as TestMonth,

Year(TestDate) as TestYear,

Week(TestDate)as TestWeek,

Day(TestDate) As TestDay,

'Q' &Ceil(Month(TestDate)/3) as Quarter,

'1' as PassRateCounter,

'NV200' as ValidatorUnit,

'Reference' as Phase,

TestID&'-'&'NV200' as SensorValuesKey;

Simple expressions like are getting calculated correctly:

count({$<Phase={'Initialisation'}>}DISTINCT UnitSerial)

Count({$<TestStatus={'Pass'},Phase={'Initialisation'}>}DISTINCT UnitSerial)

The problem is if I try a more complex one like:

sum({<Phase={'Initialisation'}>}aggr(TestStatusBool,UnitSerial))

It does not calulate correctly.

Should I try a different approach no my model?? Or its just a miswritten expression

Regards,

Re: Concatenate fact tables

swuehl — Fri, 16 Aug 2013 08:21:12 GMT

Try adding an aggregation function like sum() or only() with the set expression to your aggr() expression, too:

=sum({<Phase={'Initialisation'}>}aggr( sum({<Phase={'Initialisation'}>} TestStatusBool) ,UnitSerial))

Besides this, telling us that 'it does not calculate correctly' is not very helpful.

What do you expect? What do you see? How do your data look like?

Re: Concatenate fact tables

Fri, 16 Aug 2013 08:33:05 GMT

No luck,

My data look like the attached image above

The first time pass rate should be lower than it calculates....

I should be getting 560 insted of 618 ftp

Expr:

count({$<Phase={'Initialisation'}>}DISTINCT UnitSerial)--->tested

Count({$<TestStatus={'Pass'},Phase={'Initialisation'}>}DISTINCT UnitSerial)--->passed

Re: Concatenate fact tables

swuehl — Fri, 16 Aug 2013 08:38:30 GMT

Expr:
count({$<Phase={'Initialisation'}>}DISTINCT UnitSerial)--->tested
Count({$<TestStatus={'Pass'},Phase={'Initialisation'}>}DISTINCT UnitSerial)--->passed

Hm, I think I am missing the expression for FTP?

Re: Concatenate fact tables

Fri, 16 Aug 2013 08:44:32 GMT

Thanks for the quick responce swuehl...Only() did the trick

Re: Concatenate fact tables

Fri, 16 Aug 2013 10:20:18 GMT

Hi swuehl,

I have an extra question for you,.....

In the same model I have the straight table results like below:

Is it possible to tranform them like :

or at least add the products with no test results in mu straight table like above.

Thanks in advance for your precious time.

Re: Concatenate fact tables

swuehl — Fri, 16 Aug 2013 10:23:47 GMT

You can set the straight table chart layout to horizontal in presentation tab of chart properties.

Also try unchecking 'suppress zero values' in the same tab.

Re: Concatenate fact tables

Fri, 16 Aug 2013 10:28:42 GMT

Horizontal ..check!!

About the suppress zero it is not going to solve the problem because I actualy do not bring in any data from products that had no test. In a way I want to create some dummy data or something like this...Is that possible??