Split data

[#1]

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.

[#2]

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):

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
//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)
;

[#3]

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.

[#4]

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

[#5]

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

[#6]

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;

[#7]

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.

[#8]

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.

[#9 · Accepted solution]

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;

[#10]

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