Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
giovanneb
Contributor II

Treatment for files cash receipts

Hello community, I need help

I have a table with monthly bills and this table have cases of chargebacks in the table that the situation would be "COD_MOV" = 11 and when that happens I need to ignore the previous payments, ie desconsidero payment before the reversal in such cases I able to treat using a flag to mark the fields, however, there are cases in which I have more than one payment in months extorno for previous payments, my doubt and how to deal with this situation, follow the attached qvw

in waiting, thanks

as it should be the document using the flags

COTA DATE COD_MOV VLRFLAG
1 01/01/201310100
1 01/02/2013101001
1 01/02/201311-1001
1 02/02/201310100
1 01/03/2013101001
1 01/03/2013102001
1 01/03/201311-1001
1 01/03/201311-2001
1 02/03/201310100
1 02/03/201310200
1 01/04/201310100
10 Replies
Not applicable

Re: Treatment for files cash receipts

I am not entirely sure what the problem you are having is. Do you want to be able to identify the records with multiple Chargebacks? Do you want to identify the records where the same payment is reversed twice? Knowing what you want will help me help you.

Si quieres, digamelo en espanol.

giovanneb
Contributor II

Re: Treatment for files cash receipts

Hola amigo, tengo una tabla con los recibos mensuales y tienen casos de devoluciones de cargos de la mesa que la situación sería "COD_MOV" = 11 y cuando sucede que necesito estas devoluciones de cargos ignoran los pagos anteriores, es decir, que desconsidero inversión y el pago antes de la inversión en este tipo de casos me las arreglé para tratar el uso de una bandera para marcar los campos, sin embargo, hay casos en que voy a tener más de una reversión de los pagos anteriores, mis dudas y la forma de manejar esta situación de la siguiente qvw caso

cómo debe ser el documento

COTA DATE COD_MOV VLRFLAG
1 01/01/201310100
1 01/02/2013101001
1 01/02/201311-1001
1 02/02/201310100
1 01/03/2013101001
1 01/03/2013102001
1 01/03/201311-1001
1 01/03/201311-2001
1 02/03/201310100
1 02/03/201310200
1 01/04/201310100
Not applicable

Re: Treatment for files cash receipts

Okay, so if I understand you correctly, you are looking for help in correctly creating the flags.

It would be easier to do if your tables had a Transaction ID field or a user ID field, however I think I can help you out.

Your table 1 has the Cod_Mov 11s flagged. You could create the table you wanted with a SQL join by selecting T.*, T1.Flag

from T left join T1

on T.Date = T1.Date and (T.VLR = T1.VLR OR T.VLR = -1*T1.VLR).

giovanneb
Contributor II

Re: Treatment for files cash receipts

Hello friend must create the correct flags, but the table has ID, COTA is the ID, I have only one record for example, careful handling this case dates coincided with the date on which payment is equal to the date of reversal however they may be different

Not applicable

Re: Treatment for files cash receipts

That makes it more of a problem. What would you do if the file you wanted looked like this?

COTA  Date        Cod_mov VLR  Flag

2         1/1/2013  10           100   1

2         1/1/2013  10           100   0

2         1/1/2013  10           200   0

2         1/1/2013  10           100   0

2         1/2/2013  11           -100  1

In this case you do not know which of the transactions to apply it to. Rows 1, 3, and 4 are identical except for the fact that one of those transactions was reversed. Does your system have any extra information that could be used to identify these transactions? If not, you may want to create one. Consider:

COTA  Date        Cod_mov VLR  Flag TransID

2         1/1/2013  10           100   1      0001

2         1/1/2013  10           100   0      0002

2         1/1/2013  10           200   0      0003

2         1/1/2013  10           100   0      0004

2         1/2/2013  11           -100  1      0001

This clearly tells you that it is the first transaction that you mean to reverse.

giovanneb
Contributor II

Re: Treatment for files cash receipts

the system does not have anything else only the fields listed in the example, how can I handle this situation the way you suggested

Not applicable

Re: Treatment for files cash receipts

.... That's tough.

I would say, order by Cota, Date, VLR, Cod_Mov, and then maybe use the equation if(below(flag,1)=1,1, ) as flag.

giovanneb
Contributor II

Re: Treatment for files cash receipts

Download the qvw it takes is mark the file flags of the way down, I'm Brazilian dates are at the default location

thanks

ID_COTADATECOD_MOVVLR
1 01/01/201310100
1 01/02/201310100
1 01/02/201311-100
1 02/02/201310100
1 01/03/201310100
1 01/03/201310200
1 01/03/201311-100
1 01/03/201311-200
1 02/03/201310100
1 02/03/201310200
1 01/04/201310100
Not applicable

Re: Treatment for files cash receipts

Sorry, think I'm out of ideas....

Community Browser