Skip to main content

QlikView Integrations

Discussion Board for collaboration on QlikView Integration.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
nguyettran_uct
Contributor
Contributor

Peek Funtion

Hi all,

I have a case as below, please give me some advises to correct the formula

Date sample: 

Customer code Date Amount of Billing  Closing Balance 
092 01/01/2022                          5,000                     100,000
092 01/02/2022                          6,000  
092 01/03/2022                          7,000  
092 01/04/2022                          8,000  
193 01/01/2022                          1,000                     150,000
193 01/02/2022                          2,000  
193 01/03/2022                          3,000  
193 01/04/2022                          4,000  

 

Desired reults

Customer code Date  Amount of Billing   Closing Balance 
092 01/01/2022                          5,000                     100,000
092 01/02/2022                          6,000                       94,000
092 01/03/2022                          7,000                       87,000
092 01/04/2022                          8,000                       79,000
193 01/01/2022                          1,000                     150,000
193 01/02/2022                          2,000                     148,000
193 01/03/2022                          3,000                     145,000
193 01/04/2022                          4,000                     141,000

 

I group and order data by Customer Code and Date, the formula is:

If(IsNull("Closing Balance") ,Peek("Closing Balance")  - "Amount of Billing", "Closing Balance") as "Closing Balance"

The problem is that the results is not separate by Customer code, it only use the "Closing Balance" of the first Customer and combine "Amount of Billing" of all customer with the same Date to calculate

Labels (1)
  • Other

2 Solutions

Accepted Solutions
vchuprina
Specialist
Specialist

Hi,

Try the following code:

DATA_TMP:
LOAD * Inline[
Customer code,  Date, Amount of Billing,   Closing Balance
092, 01/01/2022,                              5000,                     100000
092, 01/02/2022,                              6000,
092, 01/03/2022,                              7000,
092, 01/04/2022,                              8000,
193, 01/01/2022,                              1000,                     150000
193, 01/02/2022,                              2000,
193, 01/03/2022,                              3000,
193, 01/04/2022,                              4000,
];

DATA:
NoConcatenate
LOAD
     [Customer code],    
     Date,
     [Amount of Billing],
     IF([Customer code] = Previous([Customer code]), Peek("Closing Balance")-[Amount of Billing], [Closing Balance])  AS [Closing Balance]
Resident DATA_TMP
Order By [Customer code], Date;
DROP Table DATA_TMP;

 

My result:

vchuprina_0-1650874747345.png

 

Regards,

Vitalii

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").

View solution in original post

nguyettran_uct
Contributor
Contributor
Author

Hi  Vchuprina, 

It's  work, thank you for your support 😀

PREVIEW
 

View solution in original post

3 Replies
vchuprina
Specialist
Specialist

Hi,

Try the following code:

DATA_TMP:
LOAD * Inline[
Customer code,  Date, Amount of Billing,   Closing Balance
092, 01/01/2022,                              5000,                     100000
092, 01/02/2022,                              6000,
092, 01/03/2022,                              7000,
092, 01/04/2022,                              8000,
193, 01/01/2022,                              1000,                     150000
193, 01/02/2022,                              2000,
193, 01/03/2022,                              3000,
193, 01/04/2022,                              4000,
];

DATA:
NoConcatenate
LOAD
     [Customer code],    
     Date,
     [Amount of Billing],
     IF([Customer code] = Previous([Customer code]), Peek("Closing Balance")-[Amount of Billing], [Closing Balance])  AS [Closing Balance]
Resident DATA_TMP
Order By [Customer code], Date;
DROP Table DATA_TMP;

 

My result:

vchuprina_0-1650874747345.png

 

Regards,

Vitalii

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").
nguyettran_uct
Contributor
Contributor
Author

Hi  Vchuprina, 

It's  work, thank you for your support 😀

PREVIEW
 
vchuprina
Specialist
Specialist

Welcome!

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").