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.
Not applicable

Loop

Hi,

I am new to qlikview not sure how can I perform following functionalities in qlikview (Kindly see the attached image for more information on data)

Fix offset:

Load

     ID number

     Description

     Amount1

     Amount2

     Total

(Under one ID I have multiple lines of descriptions and related amount1 or amount2)

Based on ID number check if any values of amount1 is equal to any values in amount2. If cell value matches then changes values of corresponding rows in total to zero. Like C and -C in attached file to zero. Its not necessary that they can be next to each other.

Loop this on n number of ID number .

Based on ID I am trying to match values in same colored cells. For example 2000 and -2000. In this fashion I have to check n number of ID.

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Loop

// your data, from excel

//

X:

LOAD ID,

    Description,

    debitAmount,

    creditAmount,

    Total,

    if(debitAmount>0, debitAmount, creditAmount) as Amt,

    if(debitAmount>0, 'D', 'C') as Type

FROM

Test_Data.xlsx

(ooxml, embedded labels, table is Sheet2)

Where len(trim(ID))>0;

// add some test data to your excel (to check different credit/debit amount)

//

load *,

    creditAmount - debitAmount as Total,

    if(debitAmount>0, debitAmount, creditAmount) as Amt,

    if(debitAmount>0, 'D', 'C') as Type;

LOAD div(IterNo()+1,2) as ID,

    'desc ' as Description,

    if(Even(iterno()), floor(rand()*100),0) as debitAmount,

    if(not Even(iterno()), floor(rand()*100),0) as creditAmount

AutoGenerate 1

While IterNo() <= 10

;

// read X table order by ID, Amt, Type

// add ID3: ID3 of previous record + 1 if ID, Amt and Type are the same, else ID3=1

// ID3 is a counter (1, 2, 3) in the group ID, Amt, Type; when ID or Amt or Type change, restart with 1

Y:

load

  *,

  Peek('ID'), Peek('Amt'), Peek('Type'), Peek('ID2'), //subfield(Peek('ID2'),'-',3)+1,

  if(Peek('ID')=ID and Peek('Amt')=Amt and Peek('Type')=Type, Peek('ID3')+1, 1) as ID3

resident X order by ID, Amt, Type;

// read Y table, just add ID4 as ID-Amt-ID3

Z:

load *, ID & '-' & Amt & '-' & ID3 as ID4

resident Y order by ID, Amt, Type;

DROP Table X, Y;

this is the Z table

1.png


// set 0 debit when ID4 = ID4 of previous record and Type <> Type of prev record

// order by Type, so Credit then Debit

// remember ID4 is the same for same ID, amount (debit/credit); pay attention to the counter (1 2) ath the end of ID4; same ID4, different Type, I can put the amount to 0

F1:

NoConcatenate

load ID,

    Description,

    debitAmount,

    creditAmount,

    Total,

    Type,

    ID4,

    Amt,

  if(Peek('ID4') = ID4 and Peek('Type') <> Type, 0, debitAmount) as NewDebitAmount

resident Z order by ID4, Amt, Type;

// the same but order by Debit, Credit and set 0 in credit

// also add the NewTotal

F2:

load *, NewCreditAmount - NewDebitAmount as NewTotal;

load ID,

    Description,

    debitAmount,

    creditAmount,

    Total,

    Type,

    ID4,

    NewDebitAmount,

  if(Peek('ID4') = ID4 and Peek('Type') <> Type, 0, creditAmount) as NewCreditAmount

resident F1 order by ID4, Amt, Type desc;

DROP Table Z, F1;

10 Replies

Re: Loop

What's your expected result for this data file?

What about the other matching values in this example?

Not applicable

Re: Loop

Kindly see the attached image. Since all debit amount matches with credit amount total will be zero for all of themResult.png

Not applicable

Re: Loop

All other lines where debit and credit amount does not match should have total amount as it.

gajulagireesh
New Contributor II

Re: Loop

Hi Nikhil

                        please check the attached file as per u uploaded excel file and let me know what is ur exact requirement

Regards,

gireesh

Not applicable

Re: Loop

Hi Gireesh,

Kindly see the attached files in below comments.

Requirement:

Based on ID number check if any values of amount1 is equal to any values in amount2. If cell value matches then changes values of corresponding rows in total to zero. Like C and -C in attached file to zero. Its not necessary that they can be next to each other.

Loop this on n number of ID number

MVP
MVP

Re: Loop

see attachment, result in image, last field, NewTotal

1.png

Not applicable

Re: Loop

Can you describe what technique you have applied and how did you achieve the last column as shown above.

Thanks in advance

MVP
MVP

Re: Loop

script is in the attachment (.qvw); can you see it or I should post the script?

MVP
MVP

Re: Loop

// your data, from excel

//

X:

LOAD ID,

    Description,

    debitAmount,

    creditAmount,

    Total,

    if(debitAmount>0, debitAmount, creditAmount) as Amt,

    if(debitAmount>0, 'D', 'C') as Type

FROM

Test_Data.xlsx

(ooxml, embedded labels, table is Sheet2)

Where len(trim(ID))>0;

// add some test data to your excel (to check different credit/debit amount)

//

load *,

    creditAmount - debitAmount as Total,

    if(debitAmount>0, debitAmount, creditAmount) as Amt,

    if(debitAmount>0, 'D', 'C') as Type;

LOAD div(IterNo()+1,2) as ID,

    'desc ' as Description,

    if(Even(iterno()), floor(rand()*100),0) as debitAmount,

    if(not Even(iterno()), floor(rand()*100),0) as creditAmount

AutoGenerate 1

While IterNo() <= 10

;

// read X table order by ID, Amt, Type

// add ID3: ID3 of previous record + 1 if ID, Amt and Type are the same, else ID3=1

// ID3 is a counter (1, 2, 3) in the group ID, Amt, Type; when ID or Amt or Type change, restart with 1

Y:

load

  *,

  Peek('ID'), Peek('Amt'), Peek('Type'), Peek('ID2'), //subfield(Peek('ID2'),'-',3)+1,

  if(Peek('ID')=ID and Peek('Amt')=Amt and Peek('Type')=Type, Peek('ID3')+1, 1) as ID3

resident X order by ID, Amt, Type;

// read Y table, just add ID4 as ID-Amt-ID3

Z:

load *, ID & '-' & Amt & '-' & ID3 as ID4

resident Y order by ID, Amt, Type;

DROP Table X, Y;

this is the Z table

1.png


// set 0 debit when ID4 = ID4 of previous record and Type <> Type of prev record

// order by Type, so Credit then Debit

// remember ID4 is the same for same ID, amount (debit/credit); pay attention to the counter (1 2) ath the end of ID4; same ID4, different Type, I can put the amount to 0

F1:

NoConcatenate

load ID,

    Description,

    debitAmount,

    creditAmount,

    Total,

    Type,

    ID4,

    Amt,

  if(Peek('ID4') = ID4 and Peek('Type') <> Type, 0, debitAmount) as NewDebitAmount

resident Z order by ID4, Amt, Type;

// the same but order by Debit, Credit and set 0 in credit

// also add the NewTotal

F2:

load *, NewCreditAmount - NewDebitAmount as NewTotal;

load ID,

    Description,

    debitAmount,

    creditAmount,

    Total,

    Type,

    ID4,

    NewDebitAmount,

  if(Peek('ID4') = ID4 and Peek('Type') <> Type, 0, creditAmount) as NewCreditAmount

resident F1 order by ID4, Amt, Type desc;

DROP Table Z, F1;

Community Browser