Get the value from a field of last month if that same field don't have value in current month

[ArjunChandra]

Hi All, Thanks for the people who are spending their time on this post. Really appreciate your time

I have few months(Jan,Feb,Mar,Apr) data like in the below picture and also attached excel file:  every month has same fields.  new customers will be added in coming months or some may change their branch.

I want to see the Current month(April) or Current selection month Transaction History, if the Customer don't have the data in the current month(Like Arjun and Srinu), they should get the values from previous month(March). If march also does't have the data then it should get from February. if there also no data then get from Jan month.

April

[kaushiknsolanki]

Hi,

Do you want this to be done on Frontend or backend?

[ArjunChandra]

As I am  new to Qlik , Frontend and Backend both will help me to learn new things.

[Saravanan_Desingh · Accepted solution]

One solution is:

Set vMon = 'Jan','Feb','Mar','Apr';
Set vMonIx = 1;

For Each Mon In $(vMon)
	tab1:
	LOAD [Customer ID], 
	     [Customer Name], 
	     BranchCode, 
	     [Cibil Rank], 
	     [Indicator Rank], 
	     Amount,
	     BranchCode As NewBranchCode,
	     Dual('$(Mon)',$(vMonIx)) As Month
	FROM
	[C:\Users\sarav\Downloads\LastTransactionData.xlsx]
	(ooxml, embedded labels, table is [$(Mon)]);
	Let vMonIx = vMonIx + 1;
Next Mon 

tab2:
NoConcatenate
LOAD RowNo() As RowID,[Customer ID], Month,BranchCode,If(IsNull(NewBranchCode), Peek(NewBranchCode), NewBranchCode) As NewBranchCode
Resident tab1
Order By [Customer ID], Month;

Drop Table tab1;

[ArjunChandra]

Thank you very much for your time and Consideration @Saravanan_Desingh  and @kaushiknsolanki .  Got the expected output.

I have a Small doubt @Saravanan_Desingh , If I replace (  NewBranchCode   in your Case ) with (BranchCode) in Is Null of IF condition of Tab2 ,still getting the same result. If you don't mind, could you tell me Why?   also what is the work of dual function there?  Appreciate your help

Set vMon ='Jan','Feb','Mar','Apr';
Set vMonIx= 1;

For Each vMonth in $(vMon)

Tab1:
LOAD [Customer ID],
[Customer Name],
[Branch Code],
[Cibil Rank],
[Indicator Rank],
Amount,
Amount as Deposit,
[Branch Code] as BranchCode,
[Cibil Rank] as CibilRank,
[Indicator Rank] as IndicatorRank,
Dual('$(vMonth)',$(vMonIx)) as Month
FROM
[C:\Users\Arjun\Desktop\Qlik Practice\SourceData\ShowLastTransactionData.xlsx]
(ooxml, embedded labels, table is $(vMonth));

LET vMonIx= vMonIx+1;

NEXT

NoConcatenate

Tab2:
LOAD [Customer ID],
[Customer Name],
Month,
if(isnull(Amount),Peek(Deposit),Deposit) as Deposit,
if(isnull([Branch Code]),Peek(BranchCode),BranchCode) as BranchCode,
if(isnull([Cibil Rank]),Peek(CibilRank),CibilRank) as CibilRank,
if(isnull([Indicator Rank]),Peek(IndicatorRank),IndicatorRank)as IndicatorRank


Resident Tab Order by [Customer ID],Month;

DROP Table Tab;

[Saravanan_Desingh · Accepted solution]

Please make sure, whatever the Fields you are changing in Tab1 is reflected in Tab2.

Set vMon = 'Jan','Feb','Mar','Apr';
Set vMonIx = 1;

For Each Mon In $(vMon)
	tab1:
	LOAD [Customer ID], 
	     [Customer Name], 
	     BranchCode, 
	     [Cibil Rank] As CibilRank, 
	     [Indicator Rank] As IndicatorRank, 
	     Amount As Deposit,
	     BranchCode As NewBranchCode,
	     Dual('$(Mon)',$(vMonIx)) As Month
	FROM
	[C:\Users\sarav\Downloads\LastTransactionData.xlsx]
	(ooxml, embedded labels, table is [$(Mon)]);
	Let vMonIx = vMonIx + 1;
Next Mon 

tab2:
NoConcatenate
//LOAD RowNo() As RowID,[Customer ID], Month,BranchCode,If(IsNull(NewBranchCode), Peek(NewBranchCode), NewBranchCode) As NewBranchCode
//Resident tab1
//Order By [Customer ID], Month;

LOAD [Customer ID],
[Customer Name],
Month,
if(isnull(Deposit),Peek(Deposit),Deposit) as Deposit,
if(isnull([BranchCode]),Peek(BranchCode),BranchCode) as BranchCode,
if(isnull([CibilRank]),Peek(CibilRank),CibilRank) as CibilRank,
if(isnull([IndicatorRank]),Peek(IndicatorRank),IndicatorRank)as IndicatorRank


Resident tab1 Order by [Customer ID],Month;
Drop Table tab1;

[Saravanan_Desingh]

In the 'Order By' I am using the Month Field, which is a String. For the Sorting Order, I am converting it in to a Number using Dual function.

More information:

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/Forma...