Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
michael_anderse
Contributor II
Contributor II

Script ID counter won't work

Hi guys

I am trying to make at ID-Count in the script, but i'm not getting the right outcome.

This is my script:

   Temp1:

     Load

           VIN,

           ServiceNo

      FROM [lib://Transform Folder/Vehicle_Ledger_Entry_20*.qvd]

     (qvd)

;

     Temp2:

     NoConcatenate

     LOAD *,

          if(ServiceNo > Previous(ServiceNo), Peek('ID_COUNTER') + 1, 1) as ID_COUNTER

     Resident Temp1

     ;

     DROP Table Temp1;

This is the outcome I get:

VINServiceNoID_COUNTERWanted outcome
WBA1J11090VW83273SO43534011
WBA1J11090VW83273SO48258822
WBA1J11090VW83273SO55180613
WBA7G4106GGE12490SO45412711
WBA7G4106GGE12490SO52297722
WBA7G4106GGE12490SO53004933
WBA7G4106GGE12490SO55939944
WBA7G4106GGE12490SO58821735
WBA7G4106GGE12490SO60921346
WBAZW41040L441783SO45235821
WBAZW41040L441783SO48492622

Can anyone help me get the right outcome?

Many thanks 🙂

/Michael

1 Solution

Accepted Solutions
agigliotti
Partner - Champion
Partner - Champion

you need to add "order by VIN, ServiceNo" after Resident Temp1

View solution in original post

9 Replies
agigliotti
Partner - Champion
Partner - Champion

replace your code with below:

if(VIN = Peek(VIN), Peek(ID_COUNTER) + 1, 1) as ID_COUNTER

michael_anderse
Contributor II
Contributor II
Author

Many thanks to you Andrea for taking the time to answer.

The outcome now changed to this, so it didn't quit do the trick

 

VINServiceNoID_COUNTER
WBA1J11090VW83273SO4353401
WBA1J11090VW83273SO4353402
WBA1J11090VW83273SO4825881
WBA1J11090VW83273SO5518061
WBA7G4106GGE12490SO4541271
WBA7G4106GGE12490SO5229771
WBA7G4106GGE12490SO5300491
WBA7G4106GGE12490SO5593991
WBA7G4106GGE12490SO5882171
WBA7G4106GGE12490SO6092131
WBAZW41040L441783SO4523581
WBAZW41040L441783SO4849261
shraddha_g
Partner - Master III
Partner - Master III

Try:

Temp1:

     Load

           VIN,

          num#(keepchar(ServiceNo,'1234567890')) as NewServiceNo,

           ServiceNo

      FROM [lib://Transform Folder/Vehicle_Ledger_Entry_20*.qvd]

     (qvd)

;

     Temp2:

     NoConcatenate

     LOAD *,

          if(NewServiceNo > Previous(NewServiceNo), Peek('ID_COUNTER') + 1, 1) as ID_COUNTER

     Resident Temp1

     ;

     DROP Table Temp1;

antoniotiman
Master III
Master III

May be this

   Temp1:

     Load

           VIN,

           ServiceNo,Autonumber(RowNo(),VIN) as ID_COUNTER

      FROM [lib://Transform Folder/Vehicle_Ledger_Entry_20*.qvd]

     (qvd)

;

     Temp2:

     NoConcatenate

     LOAD *,

          if(ServiceNo > Previous(ServiceNo), Peek('ID_COUNTER') + 1, 1) as ID_COUNTER

     Resident Temp1

     ;

     DROP Table Temp1;

Mark_Little
Luminary
Luminary

I would add am order by on your table to make sure it is load as shown.

I.e Order By VIN, ServiceNo;

Mark

agigliotti
Partner - Champion
Partner - Champion

you need to add "order by VIN, ServiceNo" after Resident Temp1

michael_anderse
Contributor II
Contributor II
Author

Many thanks, it did the job

michael_anderse
Contributor II
Contributor II
Author

Many thanks, it did the job

michael_anderse
Contributor II
Contributor II
Author

Many thanks, it did the job