topic Re: How to repeat data in a dimension in QlikView

How to repeat data in a dimension

JulesVerne — Sat, 16 Nov 2024 02:14:15 GMT

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

Re: How to repeat data in a dimension

JGMDataAnalysis — Mon, 14 Oct 2019 01:05:10 GMT

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;

Re: How to repeat data in a dimension

JulesVerne — Sun, 13 Oct 2019 20:22:21 GMT

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".....

Re: How to repeat data in a dimension

Arthur_Fong — Mon, 14 Oct 2019 00:38:24 GMT

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

Re: How to repeat data in a dimension

JGMDataAnalysis — Mon, 14 Oct 2019 01:09:26 GMT

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

Re: How to repeat data in a dimension

JulesVerne — Mon, 14 Oct 2019 20:38:45 GMT

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?

Re: How to repeat data in a dimension

JGMDataAnalysis — Mon, 14 Oct 2019 21:51:26 GMT

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.

Re: How to repeat data in a dimension

JulesVerne — Tue, 15 Oct 2019 12:26:38 GMT

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