How to repeat data in a dimension

[JulesVerne]

Dear All,

I am working on a project.. My data sourse is MS-SQL..

I have a data like :

I want to repeat  boxno to the null spaces like that :

I tried lots of method in expression but i couldnt solve

like, isnull, Last , ... fonction..

Can you help me please

Kind Regards

Jules

[JGMDataAnalysis]

Something like this assuming that the data resides in a single table (SourceTable). Otherwise, replace with the corresponding query.

Test:
NOCONCATENATE
LOAD *, If(Len(Trim(boxno)), boxno, Peek(boxno_temp)) AS boxno_temp
;
SELECT 
    Id, 
    boxno, 
    ModelCode, 
    ProductCode, 
    unit, 
    ProductId
FROM SourceTable
ORDER BY Id;

DROP FIELD boxno; RENAME FIELD boxno_temp TO boxno;

[JulesVerne]

Thanks,

I think your solution needs a little touch

It worked only one next-row after first 1 and first 2 and first 3 ...

Like :

In ID  2119088 :  boxno = 1 --> That is good but boxno of  2119089,2119090,2119091.... also must be "1"

Also others... boxno of 2119135 ID : --> is "2" comes from  up-ID  it is good...

But 2119136,2119137,2119138.... must be "2".....

[Arthur_Fong]

There you go:

Sort your data accordingly, I guess your data is sorted by ID btw.

Next, apply peek() function to grab the value above the record.

Refer qvw attached for reference.

Thanks and regards,

Arthur Fong

[JGMDataAnalysis]

I modified the original post, I changed the field that receives the Peek function as an argument.

If (Len (Trim (box)), box, Peek (boxno_temp)) AS boxno_temp

[JulesVerne]

Dear @JGMDataAnalysis 

Your answer is worked... Thanks... It was great job...

But there is a problem...

Your script is working ONLY sequential ID...

When i inspect my IDs then they are not sequential..

So 2119186 is boxno=3 and there is no 2119187 ID .. It is jumping to 2119200 and I thinh the peek() fonction is set the boxno=1

I tried to add a new ID with :

LOAD *, RowNo() as QId, If(Len(Trim(boxno)), boxno, Peek(boxno_temp)) AS boxno_temp

But the same jumping occurs here..

What should I do?

[JGMDataAnalysis]

To determine my script I assumed that ONLY the records that have been assigned the value 1595 in the ProductID field have a value in the boxno field, according to the reference images. Also, to fill in the gaps, it's crucial to sort the input table by the Id field in ascending mode. To give you a solution that fits your requirements, I need to see your complete script and if it is possible for you, to attach a file with the data of the example images.

[JulesVerne · Accepted solution]

In My SQL

same ID Jumping... It is Normal...  2119186 to 2119200  (It is same with QlikView)

And The counted record is equal (264)

AND

My full script is ( honestly your code 🙂  @JGMDataAnalysis  )

OLEDB CONNECT32 TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=xx;Data Source=yyy ;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=zzzz;Use Encryption for Data=False;Tag with column collation when possible=False] (XPassword is .......);

Test:
NOCONCATENATE
LOAD *, If(Len(Trim(boxno)), boxno, Peek(boxno_temp)) AS boxno_temp
;
SQL SELECT
Id,
OrderType,
OrderCode,
boxno,
ModelCode,
VariantCode,
PName,
ProductCode,
ProductName,
UnitGroup,
unit,
UnitPrice,
UnitTotalPrice,
Category,
UnitPackage,
ControlPrice,
ProductId
FROM KamelonMicro.dbo.CekiList
ORDER BY Id, OrderCode;

DROP FIELD boxno;
RENAME FIELD boxno_temp TO boxno;

I think, I must use different ID (sequential ID / havent any ID GAP) while loading data.. And then Peek() fonction will work fine..

Thanks for your help