Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Partner
Partner

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

View solution in original post

9 Replies
Highlighted
Partner
Partner

replace your code with below:

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

Highlighted
Contributor II
Contributor II

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
Highlighted
Partner
Partner

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;

Highlighted
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;

Highlighted
Partner
Partner

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

I.e Order By VIN, ServiceNo;

Mark

Highlighted
Partner
Partner

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

View solution in original post

Highlighted
Contributor II
Contributor II

Many thanks, it did the job

Highlighted
Contributor II
Contributor II

Many thanks, it did the job

Highlighted
Contributor II
Contributor II

Many thanks, it did the job