Problem with Script

[kishorj1982]

Hi All,

Below is my code and there are number of resident statement and also there are billions of records in a table.

DIRECTORY $(RootFolderForInputQVD6);

//Row-Optimized load of COPA data

COPA_TEMP:

first 10

LOAD

    [Sales Document Number],

    [%Currency Type_CURTYPE],

    [%Valuation view_VALUATION],

    [Currency key_CURRENCY],

    [Posting date in the document_PSTNG_DATE],

    [External SalOrd Item_/BIC/ZWWEIT],

    [External Sales Ord_/BIC/ZWWESO],

    [Sales to Profit Center]

FROM

[COPA.qvd]

(qvd);

//Data transposition into one SO line based on currency and PC Valuation

NoConcatenate

COPA_TRANSFORM_TEMP:

first 10

LOAD

[External Sales Ord_/BIC/ZWWESO] &'-'&  [External SalOrd Item_/BIC/ZWWEIT]  as [Sales Document Item Key],

[Sales to Profit Center] as  [Sales to Profit Center LC],

[Currency key_CURRENCY] as [Local Currency],

[Posting date in the document_PSTNG_DATE] as [COPA Document Date]

RESIDENT COPA_TEMP

WHERE [%Valuation view_VALUATION]=2 and [%Currency Type_CURTYPE]='10' and [Sales Document Number]<>[External Sales Ord_/BIC/ZWWESO];

concatenate(COPA_TRANSFORM_TEMP)

first 10

LOAD

[External Sales Ord_/BIC/ZWWESO] &'-'& [External SalOrd Item_/BIC/ZWWEIT] as [Sales Document Item Key],

[Sales to Profit Center] as  [Sales to Profit Center GC],

//[Currency key_CURRENCY] as [Global Currency],

[Posting date in the document_PSTNG_DATE] as [COPA Document Date]

RESIDENT COPA_TEMP

WHERE [%Valuation view_VALUATION]=2 and [%Currency Type_CURTYPE]='B0' and [Sales Document Number]<>[External Sales Ord_/BIC/ZWWESO];

//COPA line preparation

NoConcatenate

COPA_DATA_GROUPED:

first 10

LOAD

[Sales Document Item Key],

[COPA Document Date],

sum([Sales to Profit Center LC]) as [Sales to Profit Center LC],

sum([Sales to Profit Center GC]) as [Sales to Profit Center GC],

maxstring([Local Currency]) as [Local Currency]

//maxstring([Global Currency]) as [Global Currency]

RESIDENT COPA_TRANSFORM_TEMP

GROUP by [Sales Document Item Key], [COPA Document Date];

//Additional filtering of data (zero value records removed)

NoConcatenate

COPA_DATA:

first 10

LOAD

*

RESIDENT COPA_DATA_GROUPED

WHERE [Sales to Profit Center LC]<>0 or [Sales to Profit Center GC]<>0;

DIRECTORY $(RootFolderForInputQVD);

// Joins the rest of dimension information

LEFT JOIN (COPA_DATA)

first 10

LOAD DISTINCT // 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],

[%CO_AREA_PROFIT_CTR],

     [%Company code_COMP_CODE],

     [%Distribution Channel_DISTR_CHAN],

     [%Division_DIVISION],

  [%Sales doc. type_DOC_TYPE],

[%Item category_ITEM_CATEG],

     [%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',// Used as [%MaterialGroup 3_MATL_GRP_3] in Parts Sales & Deliveries.qvw

     [%MaterialGroup 4_MATL_GRP_4],

     [%MaterialGroup 5_MATL_GRP_5],

[%Reason for order_ORD_REASON],

[%Plant_PLANT],

     [%Reason for rejection_REASON_REJ],

     [%Reason for substitution_SUB_REASON],

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

     [%Sales Office_SALES_OFF],

[%Sales Representative_SALESEMPLY],

     [%Sales Organization_SALESORG],

     [%Shipping point_SHIP_POINT],

     [%Sold-to Party_SOLD_TO],

     [%Ship-To Party_SHIP_TO],

     [%Bill-to party_BILLTOPRTY],

     [%WBS Element_WBS_ELEMT],

     [%Prod.hierarchy_PROD_HIER_1],

     [%Prod.hierarchy_PROD_HIER_2],

     [%Payer_PAYER],

     [%Trading Partner_ZVBUND],

//     [%Delivery priority_DLV_PRIO],

//     [%Shipping Type_SHIP_TYPE],

//     [%Service Branch_/BIC/ZIKSDSB1],

     [%Partner profit center_PART_PRCTR],

     [%CO_AREA_PROFIT_CTR_BILLING],

     [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 ('Planned Delivery Date Map', [Sales Document Item Key], 'N/A') as [Planned Delivery Date],

ApplyMap ('Confirmed Delivery Date Map', [Sales Document Item Key], 'N/A') as [Confirmed Delivery 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],

     [Credit Status],

[Profit Center Sales Document Item Key],

     [Complete Delivery],

     [Sell and Lease Back Order],

     [Parts Brand],

     [Service Request Number],

ApplyMap ('Sales Document Header Medium Description Map', ApplyMap ('Reference Document Map', [Sales Document Number], Null ()), 'N/A') as [Service Agreement 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],

     [Sales Document Creation Date],

     [Sales Document Date],

     [Sales Document Category],

     [Sales Document Number],

[Sales Document Item],

     [Customer Purchase Order Number],

     [Customer Purchase Order Item],

     [Customer Material Number],

     [Material Entered],

     [Base Unit],

     [Group Currency],

     [Local Currency],

     [Document Currency],

     [Parts Portal Web Number] as 'Ecommerce Number',

     [Parts Portal Web Number Exists] as 'Ecommerce Number Exists',

     [Project No],

     [Customer Reference],

     [Crane Serial Number],

ApplyMap ('Purchasing Document Type Map', [Sales Document Number], ' ') as [%Purchasing document type_DOCTYPE],

[Delivery Group],

[Target Price LC],

     [Target Price DC]

FROM

[SalesOrder.qvd] (qvd)

// Do not retrieve offers (sales document types ZQT and ZQH)

WHERE Match ([%Sales doc. type_DOC_TYPE], 'ZQT', 'ZQH') = 0

// Only not rejected orders can have orderbook values

AND [%Reason for rejection_REASON_REJ] = ' '

AND [%Company code_COMP_CODE] <> 'CN05' and [%Company code_COMP_CODE] <> 'US04'  // Removing Stahl data

;

// Concatenates COPA data to Data fact table

CONCATENATE (Data)

first 10

LOAD

*,

AutoNumber ([Sales Document Item Key] & '-' & [COPA Document Date], 'Date Key') as [Date Key],

'COPA Sales' as QVDsource

RESIDENT COPA_DATA

// Removing Stahl data

WHERE [%Company code_COMP_CODE] <> 'CN05' and [%Company code_COMP_CODE] <> 'US04';

DROP TABLE COPA_TEMP;

DROP TABLE COPA_TRANSFORM_TEMP;

DROP TABLE COPA_DATA_GROUPED;

DROP TABLe COPA_DATA;

Could you please suggest how do I apply above transformations without resident statements.

Thanks

[tomasz_tru]

billions of records you said? Is there a possibility to move those transformations to the data warehouse/source? Qlik is not so good at GROUP BY with large volumes of data.

Tomasz

[Miguel_Angel_Baeyens]

Why do you need to get rid of RESIDENT? What is the real problem? Is the "FIRST 10" on purpose or just to avoid loading all the rows?

Yes, you can do a STORE INTO of each intermediate step and the load from there. If you are running QlikView 12.20.x the difference shouldn't be very big in favor of either RESIDENT or FROM QVD.

Is the script crashing in any particular statement or just taking too long?

In general, I agree with Tomasz, don't JOIN / GROUP BY in QlikView if you can do it in your data warehouse.

[kishorj1982]

Hi Minguel,

I used first 10 only for my reference to add limited records so that I can test it properly.

Otherwise data volume is huge in my case.

Thanks