Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

calculating count of column1 of table1 in scripting while loading table 2

Hi,

              I want to calculate count of column1 from table 1 then check the value of column 4 of table 2 and assign the above calculated count to this column.

              eg:if(column4='abc' then count(column1))

                   if(column4='def' then count(column1))

                   how can i do this in scripting?

Thank you.

6 Replies
chauhans85
Esteemed Contributor

calculating count of column1 of table1 in scripting while loading table 2

Load

count(if(column4='abc' ,column1,

                   if(column4='def' ,column1))  as fieldname

from path group by column4;

hope this helps

Not applicable

calculating count of column1 of table1 in scripting while loading table 2

I have already loaded table 1 and table 2 seperately. so shall i write the above script while loading table 2 or perform a resident load?

chauhans85
Esteemed Contributor

calculating count of column1 of table1 in scripting while loading table 2

you need to join two tables

then in resident load you can write the above code

hope this helps

Not applicable

calculating count of column1 of table1 in scripting while loading table 2

I cannot perform a join of the tables cause both the tables are linked to multiple tables and it may result in loss of information...

chauhans85
Esteemed Contributor

calculating count of column1 of table1 in scripting while loading table 2

you leave two tables as it is

but  resident of one table you can load with two fields key field and column 1

then you can add one field to onther table based on key field usng mapping load

when you add column1 into table where you column4 then easily you can apply condition in above post.

hope this help

vupen
Contributor

Re: calculating count of column1 of table1 in scripting while loading table 2

If I understand your question correctly, this is what you are looking for:

BaseTable:

Load

column1,

column2,

column3,

column4

From

******

;

ColCount:

Load

Count(column1) as Col1Count

Resident

BaseTable

;

Let vColCount = Peek('Col1Count');

Drop Table ColCount;

NewTable:

Load

column1 as col1,

column2 as col2,

column3 as col3,

If(column4='abc',$(vColCount),$(vColCount)*10) as col4

Resident

BaseTable

;

Community Browser