Creating QVD in Qlik

[kishorj1982]

Hi All,

I am using transformed QVD in my application.

But I cannot use Autonumber feuction while QVD creation.

and I am using autonumber function in data model layer.

But my results are not yet matching.

Do I need to avoid applymap and where conditions as well while QVD creation.

Below is the piece of code for QVD creation please suggest which part do I need to handle in data model layer.

Data:
LOAD // Key fields
// If Item Category is 'ZTAC', 'ZTAK' or 'ZTRO' retrieve Material from ZSD_REELS using Plant and Material Entered
If (Match ([%Item category_ITEM_CATEG], 'ZTAC', 'ZTAK', 'ZTRO'), ApplyMap ('ZSD_REELS Map', [%Plant_PLANT]& ' ' & [Material Entered], [%Material_MATERIAL]), [%Material_MATERIAL]) as [%Material_MATERIAL],
[Sales Document Item Key],
[Sales Document Number],
[Sales Document Item],
[Billing Document Item Key],
//AutoNumber ([Billing Document Item Key], 'Date Key') as [Date Key],
[Billing Document Item Key] as [Billing Document Item Count],
[Billing Document Number],
[Billing Document Number] as [Billing Document Number Count],
[Billing Document Item],
[%CO_AREA_PROFIT_CTR],
[%Company code_COMP_CODE],
[%Ship-to Sales District_SALES_DIST],// as '%Sales District_SALES_DIST', //BI-2516 - xrantsju - 20180605 - Sales District is changed back to derived from Sold to Party in SD New.
[%Sales District_SALES_DIST], //BI-2516 - xrantsju - 20180605 - Sales District is changed back to derived from Sold to Party in SD New.
[%Billing type_BILL_TYPE],
[%Billing category_BILL_CAT],
[%Sales Organization_SALESORG],
[%Distribution Channel_DISTR_CHAN],
[%Material group_MATL_GROUP],
[%MaterialGroup 1_MATL_GRP_1],
[%MaterialGroup 2_MATL_GRP_2],
[%MaterialGroup 3_MATL_GRP_3],
[%Parts Reporting Group_MATL_GRP_3] as [%Parts Reporting Group],
[%MaterialGroup 4_MATL_GRP_4],
[%MaterialGroup 5_MATL_GRP_5],
[%Sold-to Party_SOLD_TO],
[%Ship-To Party_SHIP_TO],
[%Bill-to party_BILLTOPRTY],
[%Item category_ITEM_CATEG],
[%Sales Representative_SALESEMPLY],
[%Division_DIVISION],
[%Sales Office_SALES_OFF],
[%Shipping point_SHIP_POINT],
[%Plant_PLANT],
[%Reason for order_ORD_REASON],
[%Reason for rejection_REASON_REJ],
[%Reason for substitution_SUB_REASON],
[%WBS Element_WBS_ELEMT],
[%Prod.hierarchy_PROD_HIER_1],
[%Prod.hierarchy_PROD_HIER_2],
[%Payer_PAYER],
[%Trading Partner_ZVBUND],
[%Partner profit center_PART_PRCTR],
[Internal Reference],
// Mappings
ApplyMap ('Sales Brand Map', [Sales Document Item Key], ' ') as [Sales Brand],
ApplyMap ('Loading Point Map', [Sales Document Item Key], ' ') as [%Loading point_LOAD_PT],
ApplyMap ('Delivery Status Map', [Sales Document Item Key], 'A') as [%Delivery Status_DLV_STS],
ApplyMap ('Schedule Line Category Map', [Sales Document Item Key], ' ') as [%Sched.line category_SCHD_CATEG],
ApplyMap ('First OCMC Date Map', [Sales Document Item Key], 'N/A') as [First OCMC Date],
ApplyMap ('OTCC Date Map', [Sales Document Item Key], 'N/A') as [OTCC Date],
ApplyMap ('First OTCC Date Map', [Sales Document Item Key], 'N/A') as [First OTCC Date],
ApplyMap ('Required Goods Issue Date Map', [Sales Document Item Key], 'N/A') as [Required Goods Issue Date],
ApplyMap ('Goods Receipt Date Map', [Sales Document Item Key], 'N/A') as [Goods Receipt Date],
ApplyMap ('Billing Value in PC View LC Map', [Billing Document Item Key], 0) as [Billing Value in PC View LC],
ApplyMap ('Billing Value in PC View DC Map', [Billing Document Item Key], 0) as [Billing Value in PC View DC],
ApplyMap ('Billing List Price LC Map', [Billing Document Item Key], 0) as [Billing List Price LC],
ApplyMap ('Billing List Price DC Map', [Billing Document Item Key], 0) as [Billing List Price DC],
ApplyMap ('Customer Reference Map', [Sales Document Item Key], ' ') as [Customer Reference],

If (IsNull (ApplyMap ('ZFA/ZFZ Sales Document Item Map', [Sales Document Item Key], Null ())), [Billing Document Date], ApplyMap ('ZF2/ZFP/ZF2D/ZFPD Billing Date Map', [Sales Document Item Key], Null ())) as [Billing Document Date],

[Profit Center Sales Document Item Key],
[Billing Document Creator],
[Sell and Lease Back Order],
[Parts Brand],
ApplyMap ('Sales Document Header Medium Description Map', ApplyMap ('Reference Document Map', [Sales Document Number], Null ()), 'N/A') as [Service Agreement Number],
[Service Request Number],
[Siebel Document Number],
If ([Service Request Number] <> ' ', 'SR-' & [Service Request Number], ApplyMap ('Sales Document Header Medium Description Map', ApplyMap ('Reference Document Map', [Sales Document Number], Null ()), 'N/A')) as [Siebel Key],
[Siebel Position Number],
[Customer Purchase Order Number],
[Customer Purchase Order Item],
[Customer Material Number],
[Group Currency],
[Local Currency],
[Document Currency],
[Base Unit],
[Parts Portal Web Number],
[Parts Portal Web Number Exists],
[Material Entered],
[Project No],
[Crane Serial Number],

// Figures
[Billing Quantity SU],
[Billing Quantity BU],
[Billing Subtotal 1],
[Billing Subtotal 2],
[Billing Subtotal 3],
[Billing Subtotal 4],
[Billing Subtotal 5],
[Billing Subtotal 6],
[Billing Subtotal 1 LC],
[Billing Subtotal 2 LC],
[Billing Subtotal 3 LC],
[Billing Subtotal 4 LC],
[Billing Subtotal 5 LC],
[Billing Subtotal 6 LC],
[Billing Net Value],
[Billing Net Value LC],
[Billing Net Value GC],
[Billing Cost],
[Billing Cost LC],
[Billing Cost GC],
[Billing Margin],
[Billing Margin LC],
[Billing Margin GC],
[Billing Accounting Value LC],
ApplyMap ('Purchasing Document Type Map', [Sales Document Number], ' ') as [%Purchasing document type_DOCTYPE],
//ApplyMap ('Recommended Sales Price Map', [%Sales Organization_SALESORG] & ' ' & [Material Entered] & ' ' & [Billing Document Date], Null ()) as [Recommended Sales Price],

'Billing' as QVDsource
FROM
[Billing.qvd] (qvd)
// Do not retrieve down payments (billing category P)
WHERE [%Billing category_BILL_CAT] <> 'P'
// Do not retrieve pro forma invoices
AND [SD Document Category] <> 'U'
// Do not retrieve down payments statistical invoice lines from closing invoices
AND [Statistical Values Indicator] <> 'Y'
// Removing Stahl data
AND [%Company code_COMP_CODE] <> 'CN05' and [%Company code_COMP_CODE] <> 'US04';

DIRECTORY $(VOPTQVD);

store Data into Data.QVD(qvd);
drop table Data;

[shiveshsingh]

Hi

If the key storage gets out of sync, the model breaks.Are you creating an optimised load?

[kishorj1982]

Hi,

I have attached sample file for your reference because after creation of QVD my results are not matching.

I have applied auto-number function in Data Model Layer.

Thanks

[shiveshsingh]

Unfortunately my qlik license is expired, may be someone else can look into it 🙂