topic Re: Load script question in QlikView

Load script question

jduluc12 — Sat, 16 Nov 2024 04:36:00 GMT

Hi,

My data looks like this.

Id Name Type Price

1 X G 10

2 B T 20

3 C M 20

4 - - -

5 Y G 22

6 B T 23

7 D C 20

8 - - -

9 D N 50

Now I want to add a new column "Link" to make data looks like as following:

Id Name Type Price Link

1 X G 10 1

2 B T 20 1

3 C M 20 1

4 - - -

5 Y G 22 5

6 B T 23 5

7 D C 20 5

8 - - -

9 D N 50

The script should look for the rows with Type "G" and should iterate over the subsequent rows to add the Id of Type "G" in the new column "Link" until it finds a NULL row like the row with ID "4" and "8".

Re: Load script question

sunny_talwar — Thu, 31 Jan 2019 12:20:26 GMT

Try this

Table:
LOAD *,
	 If(Type = 'G', RangeSum(Peek('Link'), 1), If(Len(Trim(Type)) = 0, ' ', Peek('Link'))) as Link;
LOAD * INLINE [
    Id, Name, Type, Price
    1, X, G, 10
    2, B, T, 20
    3, C, M, 20
    4
    5, Y, G, 22
    6, B, T, 23
    7, D, C, 20
    8
    9, D, N, 50
];

Re: Load script question

jduluc12 — Thu, 31 Jan 2019 12:22:46 GMT

If(Type = 'G', RangeSum(Peek('Link'), 1), If(Len(Trim(Type)) = 0, ' ', Peek('Link'))) as Link;
should be
If(Type = 'G', RangeSum(Peek('Id'), 1), If(Len(Trim(Type)) = 0, ' ', Peek('Id'))) as Link;
?

Re: Load script question

sunny_talwar — Thu, 31 Jan 2019 12:27:42 GMT

My bad, it will be this

Table:
LOAD *,
	 If(Type = 'G', Id, If(Len(Trim(Type)) = 0, ' ', Peek('Link'))) as Link;
LOAD * INLINE [
    Id, Name, Type, Price
    1, X, G, 10
    2, B, T, 20
    3, C, M, 20
    4
    5, Y, G, 22
    6, B, T, 23
    7, D, C, 20
    8
    9, D, N, 50
];

Re: Load script question

pradosh_thakur — Thu, 31 Jan 2019 12:29:20 GMT

Sample_Table:
load *,if(Type='G',RowNo(),IF(Len(Trim(Type))=0, ' ',peek(Link) )) as Link
Inline [
Id,Name,Type,Price
1 ,X,G,10
2,B,T,20
3,C,M,20
4,,,
5,Y,G,22
6,B,T,23
7,D,C,20
8,,,
9,D,N,50
];