topic Re: Split data in QlikView

Split data

Wed, 21 Mar 2012 21:52:08 GMT

Hello,

T:
LOAD * INLINE [
F1, F2, F3
1, a, 1
1, a, 2
1, a, 3
1, a, 4
1, a, 5
1, a, 6
1, a, 7
2, a, 8
2, b, 9
2, b, 10
2, b, 11
2, b, 12
2, b, 13
];

T2:
LOAD F1 as F,F2 as F_1,F3 as F_2
Resident T
Where F1 =1 or F2 ='a'
;

QUALIFY *;
T3:
LOAD F1 as F,F2 as F_1,F3 as F_2
Resident T
Where F1 =2 or F2 ='b'
;

When i load this data the third table it should not get the data of 2,a,8. B'coz it is already retrived in T2 table. How can i control this.

Wt is exactly want is i'm partitioning the qvd file for every 1wk.But based to F2 column started time. If the data of F2 is started in first wk and it is continue in second wk then the second wk data also retrive in first wk data instead of 2nd wk. When i'm trying to retrive in second table it should not retrive the data already loaded in first table.

Split data

Thu, 22 Mar 2012 00:42:03 GMT

Hi jacq,

Below I made 2 insertions to your script. First to create a key called myKey and second to use a where not exists to only load fields that have not been loaded in your second table (which is what I think you are trying to achieve). Below is the edited script (Hope this helps):

LOAD * INLINE [
F1, F2, F3
1, a, 1
1, a, 2
1, a, 3
1, a, 4
1, a, 5
1, a, 6
1, a, 7
2, a, 8
2, b, 9
2, b, 10
2, b, 11
2, b, 12
2, b, 13
];

T2:
LOAD F1 as F,F2 as F_1,F3 as F_2
//First Insertion
,F1&F2&F3 as myKey
Resident T
Where F1 =1 or F2 ='a'
;

QUALIFY *;
T3:
LOAD F1 as F,F2 as F_1,F3 as F_2
Resident T
Where (F1 =2 or F2 ='b')
//Second Insertion
and Not Exists(myKey, F1&F2&F3)
;

Re: Split data

Thu, 22 Mar 2012 03:25:41 GMT

Thanks a lot it's working. But i'm trying little bit more. I want to create a partition for the qvd files. This is the code which i developed.

Temp:
LOAD Distinct F1 AS DistCount
from T.qvd;

Let vCount=FieldValueCount('DistCount');

FOR i=0 to $(vCount)-1
LET vWk = Peek('DistCount',$(i),'Temp');

$(vWk):
LOAD *
From
T.qvd (qvd)
Where F1 = '$(vWk)';
STORE $(vWk) into QVD\$(vWk).qvd;
DROP Table $(vWk);
Next
DROP Table Temp;

Now i want to create partition qvd files,

For eg:

According to my first post, it should generate 2 qvd files. In second qvd file i don't want to see data of 2,a,8. It should fall in first qvd itself.

How can i achive this.

Re: Split data

Thu, 22 Mar 2012 05:14:46 GMT

hi jacq,

use this code:

Temp:
LOAD Distinct F1 AS DistCount 
FROM
T.QVD
(qvd);
Let vCount=FieldValueCount('DistCount');
FOR i=0 to $(vCount)-1
LET vWk = Peek('DistCount',$(i),'Temp');
QUALIFY *;
$(vWk):
LOAD *
From
T.QVD (qvd)
Where F1 = '$(vWk)';
STORE $(vWk) into  $(vWk).QVD;
NEXT
DROP Table Temp;

Hope it help u....

Re: Split data

Thu, 22 Mar 2012 05:34:39 GMT

Hello,

I don't required renaming the fields. When i run the qvw file I want to generate qvd files for the above mentioned data as:

FirstqvdFile Data As:

F1, F2, F3

1, a, 1

1, a, 2

1, a, 3

1, a, 4

1, a, 5

1, a, 6

1, a, 7

2, a, 8

SecondqvdFile Data As:

F1, F2, F3

2, b, 9

2, b, 10

2, b, 11

2, b, 12

2, b, 13

- But if we run the qvw file along with code which i developed it will generate as

FirstqvdFile Data As:

F1, F2, F3

1, a, 1

1, a, 2

1, a, 3

1, a, 4

1, a, 5

1, a, 6

1, a, 7

SecondqvdFile Data As:

F1, F2, F3

2, a, 8

2, b, 9

2, b, 10

2, b, 11

2, b, 12

2, b, 13

- I don't want this type of data.

- Based on F1 column only i need to generate qvd files. But if F2 values are continues in next F1 data it should fall in previous split only.

For eg:

F1 as 1,2,3,4,5...... soon

We need to create 1,2,3,4,5.....Soon qvd files.

Now we will consider only 1,2 qvd files data. If F2 of 1st split continues in 2nd split this data should fall in 1st split only

Re: Split data

Thu, 22 Mar 2012 06:20:46 GMT

hi,

u can usr F2 instead of F1 for this,

try this:

Temp:
LOAD Distinct F2 AS DistCount 
FROM
T.QVD
(qvd);
Let vCount=FieldValueCount('DistCount');
FOR i=0 to $(vCount)-1
LET vWk = Peek('DistCount',$(i),'Temp');
QUALIFY *;
$(vWk):
LOAD *
From
T.QVD (qvd)
Where F2 = '$(vWk)';
STORE $(vWk) into  $(vWk).QVD;
NEXT
DROP Table Temp;

Re: Split data

Thu, 22 Mar 2012 07:06:43 GMT

Yes you are right i know that. My problem is here again:

F1, F2, F3

1, a, 1

1, a, 2

1, a, 3

1, a, 4

1, b, 5

1, b, 6

1, b, 7

2, c, 8

2, c, 9

2, c, 10

2, c, 11

2, c, 12

2, c, 13

Now F2 has a and b for F1=1.

So in first split i should get a and b. But if i use ur code instead of generating 2 qvds it will generate 3 qvd files. See my first priority in where condition for F1. Based of F1 only the data should split.

Re: Split data

Thu, 22 Mar 2012 09:29:23 GMT

At last i got the solution. But i need to move little bit forward to end my task, here is the code.

Temp:
LOAD Distinct F1 as DF1,F2 as DF2
FROM
Month\Test.qvd
(qvd);
LET vCount = FieldValueCount('DF1');
LET vFile =0;
FOR i=0 to $(vCount)-1
LET vWeek = Peek('DF1',$(i),'Temp');
LET vWeek1 = Peek('DF2',$(i),'Temp');
if $(vFile) <> 0 then
          LOAD myKey as myKey1
          FROM
          Month\$(vFile).qvd
          (qvd);
          $(vWeek):
          LOAD *,
          F1&F2 as myKey
          FROM
          Month\Test.qvd
          (qvd)
          Where (F1 = '$(vWeek)' or F2 = '$(vWeek1)')
          and Not Exists(myKey1, F1&F2)
          ;
          STORE $(vWeek) into Month\$(vWeek).qvd;
          DROP Table $(vWeek);
ELSE
          $(vWeek):
          LOAD *,
          F1&F2 as myKey
          FROM
          Month\Test.qvd
          (qvd)
          Where F1 = '$(vWeek)' or F2 = '$(vWeek1)'
          ;
          STORE $(vWeek) into Month\$(vWeek).qvd;
          DROP Table $(vWeek);
ENDIF
LET vFile=$(vWeek);
Next
LET vFile=0;

- But now what i want is , If data is like shown below how can i split the data based on F1 and F2

F1, F2, F3

1, a, 1

1, a, 2

1, a, 3

1, a, 4

1, b, 5

1, b, 6

1, b, 7

2, b, 8

2, c, 9

2, c, 10

2, c, 11

2, c, 12

2, c, 13

- My first priority is the data should split based on F1 only But 2,b,8 should create in first qvd file only.

Re: Split data

Thu, 22 Mar 2012 10:04:27 GMT

Today i have done good job. At last i'got the solution for my entire task, Here is the FULL CODE

Temp:
LOAD Distinct F1 as DF1,F2
FROM
Month\Test.qvd
(qvd);
LET vCount = FieldValueCount('DF1');
LET vFile =0;
FOR i=0 to $(vCount)-1
LET vWeek = Peek('DF1',$(i),'Temp');
Temp1:
LOAD Distinct F2 as DF2
Resident Temp
Where DF1 = '$(vWeek)'
Order By F2 asc;
;
LET vWeek1 = Peek('DF2',-1,'Temp1');
if $(vFile) <> 0 then
          LOAD myKey as myKey1
          FROM
          Month\$(vFile).qvd
          (qvd);

          $(vWeek):
          LOAD *,
          F1&F2 as myKey
          FROM
          Month\Test.qvd
          (qvd)
          Where (F1 = '$(vWeek)' or F2 = '$(vWeek1)')
          and Not Exists(myKey1, F1&F2)
          ;
          STORE $(vWeek) into Month\$(vWeek).qvd;
          DROP Table $(vWeek);

ELSE
          $(vWeek):
          LOAD *,
          F1&F2 as myKey
          FROM
          Month\Test.qvd
          (qvd)
          Where F1 = '$(vWeek)' or F2 = '$(vWeek1)'
          ;
          STORE $(vWeek) into Month\$(vWeek).qvd;
          DROP Table $(vWeek);
ENDIF
LET vFile=$(vWeek);
Next
LET vFile=0;
DROP Table Temp1;
DROP Table Temp;

Re: Split data

Thu, 22 Mar 2012 10:05:37 GMT

Thanks to ianmcgivern . B'coz he had given a way to achive my task.

Re: Split data

Thu, 22 Mar 2012 21:40:42 GMT

No Worries jacq, glad to have been some help.