Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys
I am trying to make at ID-Count in the script, but i'm not getting the right outcome.
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:
VIN | ServiceNo | ID_COUNTER | Wanted outcome |
WBA1J11090VW83273 | SO435340 | 1 | 1 |
WBA1J11090VW83273 | SO482588 | 2 | 2 |
WBA1J11090VW83273 | SO551806 | 1 | 3 |
WBA7G4106GGE12490 | SO454127 | 1 | 1 |
WBA7G4106GGE12490 | SO522977 | 2 | 2 |
WBA7G4106GGE12490 | SO530049 | 3 | 3 |
WBA7G4106GGE12490 | SO559399 | 4 | 4 |
WBA7G4106GGE12490 | SO588217 | 3 | 5 |
WBA7G4106GGE12490 | SO609213 | 4 | 6 |
WBAZW41040L441783 | SO452358 | 2 | 1 |
WBAZW41040L441783 | SO484926 | 2 | 2 |
Can anyone help me get the right outcome?
Many thanks 🙂
/Michael
you need to add "order by VIN, ServiceNo" after Resident Temp1
replace your code with below:
if(VIN = Peek(VIN), Peek(ID_COUNTER) + 1, 1) as ID_COUNTER
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
VIN | ServiceNo | ID_COUNTER |
WBA1J11090VW83273 | SO435340 | 1 |
WBA1J11090VW83273 | SO435340 | 2 |
WBA1J11090VW83273 | SO482588 | 1 |
WBA1J11090VW83273 | SO551806 | 1 |
WBA7G4106GGE12490 | SO454127 | 1 |
WBA7G4106GGE12490 | SO522977 | 1 |
WBA7G4106GGE12490 | SO530049 | 1 |
WBA7G4106GGE12490 | SO559399 | 1 |
WBA7G4106GGE12490 | SO588217 | 1 |
WBA7G4106GGE12490 | SO609213 | 1 |
WBAZW41040L441783 | SO452358 | 1 |
WBAZW41040L441783 | SO484926 | 1 |
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;
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;
I would add am order by on your table to make sure it is load as shown.
I.e Order By VIN, ServiceNo;
Mark
you need to add "order by VIN, ServiceNo" after Resident Temp1
Many thanks, it did the job
Many thanks, it did the job
Many thanks, it did the job