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: 
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
SunilChauhan
Champion
Champion

Load

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

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

from path group by column4;

hope this helps

Sunil Chauhan
Not applicable
Author

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?

SunilChauhan
Champion
Champion

you need to join two tables

then in resident load you can write the above code

hope this helps

Sunil Chauhan
Not applicable
Author

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

SunilChauhan
Champion
Champion

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

Sunil Chauhan
vupen
Partner - Creator
Partner - Creator

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

;