Add a column value into other column

[Rsaiq]

Hi All,

We have a datasource where 2 amount columns are there i.e Cashflows and Invest

Now we need to select cashflows amount where TYPE =03.Dividend Income and Type=02.Sale and store in invest column. We need to replace the existing amount of Invest with cashflows amount.

Please help me to resolve this issue

Kindly find attached sample data sheet

Thanks

[avinashelite]

try like this 

if(TYPE ='03.Dividend Income' or  Type='02.Sale', cashflows,Invest) as Invest,

[micheledenardi · Accepted solution]

You can achieve this in 2 ways, directly in the table using set analysis or during load script:

Via Set Analysis, use this as new Invest Expression:

Sum({<Type={'03.Dividend Income','02.Sale'}>}Cashflows)
+
Sum({<Type=-{'03.Dividend Income','02.Sale'}>}Invest)

 

Or, during load Script:

LOAD
    ScriptID,
    Account,
    "Date",
    "Type",
    Cashflows,
    Invest,
    If(Match(Type,'03.Dividend Income','02.Sale'),Cashflows,Invest) as [Invest New]
FROM [lib://BaseFolder/3.DEV/_Temp/Sample.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);

[Rsaiq]

Thanks micheledenardi...

Its working well as per my requiremet

Could you please tell me how can i change the amount from negative to positive for max date only.

 In Above table max date is 31/12/2020 and amount is -1040000 in invest_New column,i need that amount to be in positive i.e 1040000.

Thanks once again.

[Rsaiq]

Thanks avinashellite for reply will try this too.

[micheledenardi]

Create a variable which contains the maximum date, then use it into your expression:

(
	Sum({<Type={'03.Dividend Income','02.Sale'}>}Cashflows)
	+
	Sum({<Type=-{'03.Dividend Income','02.Sale'}>}Invest)
)
*
if(Date=vMaxDate,-1,1)

Last column contains above expression:

[Rsaiq · Accepted solution]

Thanks a lot micheledenardi...

Its working ...

[Rsaiq]

Hi Micheledenardi,

Could you please look on to below issue.

https://community.qlik.com/t5/New-to-Qlik-Sense/Creating-column-from-existing-column/m-p/1821473#M18...

Thanks