Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
DRneel
Contributor III
Contributor III

populate a new column in a table based on conditions on other columns

Hello Friends and Experts, 

                         I need some help on the following:

I have been trying to develop an app where I need to make different comparisons between different column values.

The input  table is as follows:

INPUT TABLE:    
BOND_IDpurchase_datematrutiy_dateVALUETypeprev_curr flag
( 1 =curr, 0 = prev)
123412/03/202031/12/20255000EXT1
123412/03/202031/12/20254800INT0
111213/03/201731/02/202066000EXT0
111413/03/202131/02/202070000EXT1

 

EXPECTED OUTPUT

:      
BOND_IDpurchase_datematrutiy_dateVALUETypeprev_curr flag
( 1 =curr, 0 = prev)
Reason
123412/03/202031/12/20255000EXT1Type change
123412/03/202031/12/20254800INT0 
111213/03/201731/02/202066000EXT0BOND matured
111413/03/202131/02/202070000EXT1Newly purchased

Here the BOND_ID is the key and prev_curr_flag is a column which is populated based on my previous period= 01st Dec, 2020 and current period=31st Mar, 2021.

Now, I need to make some comparison like,

1. as for first 2 rows BOND_ID are same for 2 different period, but Type is different then I need to highlight TYPE column for those 2 rows or mention in a separate column "REASON", i.e. the type has changed

2. for row 3, as the BOND_ID does not exist for current period and we can see that it's matured (maturity_date) before current period, hence need to highlight the maturity date column for this row or or mention in a separate column "REASON", i.e. the BOND has matured

2. for row 4, as the BOND_ID does not exist for previous period and we can see that it's purchased (purchase_date) after previous period, hence need to highlight the purchase date column for this row or mention in a separate column "REASON", i.e. the BOND is newly purchased.

I have attached an excel for the input table and expected result. Could you please help how this can be achieved? in case you have any other question please let me know.

Labels (1)
1 Reply
DRneel
Contributor III
Contributor III
Author

I was trying the following,

after the  INPUT TABLE is loaded in put the script,

INPUT:

LOAD
BOND_ID,
purchase_date,
matrutiy_date,
contribution,
BOND_Type,
prev_curr_flag
FROM [lib://AttachedFiles/TEST_QS_INPUT.xlsx];

 

Left Join(INPUT)
LOAD
IF( ([BOND_ID]=PREVIOUS([BOND_ID]) ) and ([BOND_Type]<> PREVIOUS([BOND_Type])), 'TYPE CHANGED','') as REASON
RESIDENT INPUT
ORDER BY BOND_ID;

my output looks like the attached file where multiple rows get created extra.

Can anybody help me please?