Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

michael_anderse
New 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
Honored Contributor II

Re: Script ID counter won't work

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

9 Replies
agigliotti
Honored Contributor II

Re: Script ID counter won't work

replace your code with below:

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

michael_anderse
New Contributor II

Re: Script ID counter won't work

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
Honored Contributor III

Re: Script ID counter won't work

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
Honored Contributor III

Re: Script ID counter won't work

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;

mark6505
Valued Contributor III

Re: Script ID counter won't work

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
Honored Contributor II

Re: Script ID counter won't work

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

michael_anderse
New Contributor II

Re: Script ID counter won't work

Many thanks, it did the job

michael_anderse
New Contributor II

Re: Script ID counter won't work

Many thanks, it did the job

michael_anderse
New Contributor II

Re: Script ID counter won't work

Many thanks, it did the job

Community Browser