Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

chrisgoudy
New Contributor II

QVD File

I need to create QVD files for 3 separate data tables.

I see that there are THREE ways to creat a QVD. 

Are one of these easier then the others, or more standard?

Would you recommend choosing a certain one?

1 Solution

Accepted Solutions
sokkorn_cheav
Honored Contributor

QVD File

Hi,

This must be after load statement.

Member:

left keep (Transactions)//LEFT KEEP ... second table should be reduced to its common intersection with the first table before being stored in QlikView

LOAD @1 as Status,

           @2 as CardNo,

           (num(@3)&'|'&@2) as %_MemberCardNoKey,

           @3 as MemberNo, 

           @4 as Surname,

           @5 as Initials,

           @6 as Title,

            Capitalize(@7) as FirstName,

           (Capitalize(@4)&' , '&Capitalize(@7)) as Fullname,

           @8 as CashCustomerNo,

           @9 as AccNo,

           @10 as PHMProfileNo,

           @11 as AddressLine1,

           @12 as AddressLine2,

           @13 as AddressLine3,

           @14 as PostalCode,

           @15 as DeliveryAddressLine1,

           @16 as DeliveryAddressLine2,

           @17 as DeliveryAddressLine3,

           @18 as DeliveryPostalCode,

           @19 as AcceptDate,

           @20 as JoinDate,

           @21 as FacilityDate,

           @22 as ExpiryDate,

           @23 as PinNo,

           @24 as TelephoneNoHome,

           @25 as TelephoneNoWork,

           @26 as CellPhoneNo,

           @27 as Email,

           @28 as IDNo,

           @29 as DateOfBirth,

           Left(@29,4) as YearBorn,

           (Year(Today())-Left(@29,4)) as MemberAge,

           if(@30 = 'M','Male', If(@30 = 'F', 'Female','Unkown')) as Gender_Name,

           if(@31 = 'A','Afrikaans', If(@31= 'E','English','Unknown'))as Language,

           @32 as CardStatus,

           @33 as MemberType,

           @34 as UnderAgedChildren,

           @35 as Occupation,

           @36 as ScriptsHere,

           @37 as AdditionalCards,

           @38 as Exercise,

           @39 as Notify,

           @40 as CommunicationPreference,

           '1'as Flag

   

FROM

(txt, codepage is 1252, no labels, delimiter is ',', msq);

Store Member into Member.qvd;

Regards,

Sokkorn Cheav

5 Replies
Highlighted
sokkorn_cheav
Honored Contributor

QVD File

Hi,

For me the first one is better and simple.

Ex: Store mytable into xyz.qvd (qvd);

Regards,

Sokkorn Cheav

chrisgoudy
New Contributor II

QVD File

where would I place the statement in the script?

Before or After the table I want to create a QVD with?

eg.

Store Member into xyz.qvd(qvd);

Member:

left keep (Transactions)//LEFT KEEP ... second table should be reduced to its common intersection with the first table before being stored in QlikView

LOAD @1 as Status,

           @2 as CardNo,

           (num(@3)&'|'&@2) as %_MemberCardNoKey,

           @3 as MemberNo, 

           @4 as Surname,

           @5 as Initials,

           @6 as Title,

            Capitalize(@7) as FirstName,

           (Capitalize(@4)&' , '&Capitalize(@7)) as Fullname,

           @8 as CashCustomerNo,

           @9 as AccNo,

           @10 as PHMProfileNo,

           @11 as AddressLine1,

           @12 as AddressLine2,

           @13 as AddressLine3,

           @14 as PostalCode,

           @15 as DeliveryAddressLine1,

           @16 as DeliveryAddressLine2,

           @17 as DeliveryAddressLine3,

           @18 as DeliveryPostalCode,

           @19 as AcceptDate,

           @20 as JoinDate,

           @21 as FacilityDate,

           @22 as ExpiryDate,

           @23 as PinNo,

           @24 as TelephoneNoHome,

           @25 as TelephoneNoWork,

           @26 as CellPhoneNo,

           @27 as Email,

           @28 as IDNo,

           @29 as DateOfBirth,

           Left(@29,4) as YearBorn,

           (Year(Today())-Left(@29,4)) as MemberAge,

           if(@30 = 'M','Male', If(@30 = 'F', 'Female','Unkown')) as Gender_Name,

           if(@31 = 'A','Afrikaans', If(@31= 'E','English','Unknown'))as Language,

           @32 as CardStatus,

           @33 as MemberType,

           @34 as UnderAgedChildren,

           @35 as Occupation,

           @36 as ScriptsHere,

           @37 as AdditionalCards,

           @38 as Exercise,

           @39 as Notify,

           @40 as CommunicationPreference,

           '1'as Flag

   

FROM

(txt, codepage is 1252, no labels, delimiter is ',', msq);

sokkorn_cheav
Honored Contributor

QVD File

Hi,

This must be after load statement.

Member:

left keep (Transactions)//LEFT KEEP ... second table should be reduced to its common intersection with the first table before being stored in QlikView

LOAD @1 as Status,

           @2 as CardNo,

           (num(@3)&'|'&@2) as %_MemberCardNoKey,

           @3 as MemberNo, 

           @4 as Surname,

           @5 as Initials,

           @6 as Title,

            Capitalize(@7) as FirstName,

           (Capitalize(@4)&' , '&Capitalize(@7)) as Fullname,

           @8 as CashCustomerNo,

           @9 as AccNo,

           @10 as PHMProfileNo,

           @11 as AddressLine1,

           @12 as AddressLine2,

           @13 as AddressLine3,

           @14 as PostalCode,

           @15 as DeliveryAddressLine1,

           @16 as DeliveryAddressLine2,

           @17 as DeliveryAddressLine3,

           @18 as DeliveryPostalCode,

           @19 as AcceptDate,

           @20 as JoinDate,

           @21 as FacilityDate,

           @22 as ExpiryDate,

           @23 as PinNo,

           @24 as TelephoneNoHome,

           @25 as TelephoneNoWork,

           @26 as CellPhoneNo,

           @27 as Email,

           @28 as IDNo,

           @29 as DateOfBirth,

           Left(@29,4) as YearBorn,

           (Year(Today())-Left(@29,4)) as MemberAge,

           if(@30 = 'M','Male', If(@30 = 'F', 'Female','Unkown')) as Gender_Name,

           if(@31 = 'A','Afrikaans', If(@31= 'E','English','Unknown'))as Language,

           @32 as CardStatus,

           @33 as MemberType,

           @34 as UnderAgedChildren,

           @35 as Occupation,

           @36 as ScriptsHere,

           @37 as AdditionalCards,

           @38 as Exercise,

           @39 as Notify,

           @40 as CommunicationPreference,

           '1'as Flag

   

FROM

(txt, codepage is 1252, no labels, delimiter is ',', msq);

Store Member into Member.qvd;

Regards,

Sokkorn Cheav

chrisgoudy
New Contributor II

QVD File

Awesome, Thanks for your help

sokkorn_cheav
Honored Contributor

QVD File

You are welcome

Community Browser