Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Load
count(if(column4='abc' ,column1,
if(column4='def' ,column1)) as fieldname
from path group by column4;
hope this helps
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?
you need to join two tables
then in resident load you can write the above code
hope this helps
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...
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
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
;