Storing QVD w.r.t to particular field name in script.

[rahulsud007]

Hi guys, I would like to store the table with respect to its field value.

I have a table with SalesID, Name, Zone, Cateogry   I need to store the data of each Cateogry into a qvd through QV script.

For eg.

SalesID	Name	Zone	Cateogry
1	ABC	east	Category1
2	sc	east	Category1
3	dsc	west	Category1
4	sef	west	Category1
5	aqw	north	Category2
6	wqa	north	Category2
7	asd	north	Category2

I need the date to be stored as category1.qvd, category2.qvd,

I don't want to use any hard corded functionality as I need to set it dynamically because there are more than 50 categories.

Kindly help, thanks in advance.

[tresesco · Accepted solution]

Try like:

T1:

Load * Inline [

SalesID, Name, Zone, Category

1, ABC, east, Category1

2, sc, east, Category1

3, dsc, west, Category1

4, sef, west, Category1

5, aqw, north, Category2

6, wqa, north, Category2

7, asd, north, Category2

];

Let vCount=FieldValueCount('Category');

For i=1 to $(vCount)

     Let vCategory=FieldValue('Category',$(i));

    NoConcatenate

    T11:

    Load

            *

    Resident T1 Where Category='$(vCategory)';

    STORE T11 into Category_$(vCategory).(qvd);

    DROP Table T11;

Next

[rahulpawarb]

May be this:

//Load sample data
Data:
LOAD * INLINE [
SalesID, Name, Zone, Category
1, ABC, east, Category1
2, sc, east, Category1
3, dsc, west, Category1
4, sef, west, Category1
5, aqw, north, Category2
6, wqa, north, Category2
7, asd, north, Category2
8, lmn, south, Category3
9, pqr, south, Category3
];

//Load distinct Category values; this is used for looping purpose
tmpCategory:
LOAD Distinct
     Category
Resident Data;

//Loop through distinct Category values
FOR i = 0 to NoOfRows('tmpCategory')-1

//Get the Unique Category value
LET vCategory = Peek('Category', $(i), 'tmpCategory');

//Fetch the records associated with unique Category value
NoConcatenate

$(vCategory):
LOAD *
Resident Data
Where Category = '$(vCategory)';

//Store the result into QVD
STORE $(vCategory) INTO $(vCategory).QVD(qvd);

//Drop the intermediate table
DROP Table $(vCategory);

NEXT

//Drop the temporary table
DROP Table tmpCategory;

Regards!

Rahul Pawar

[rahulsud007]

Thanks a lot tresesco‌ and Rahul both of the logic worked in this case.

Thanks a lot it was helpful