how do i improve load performance or rather make below script more efficient 

MAP_SALES_REP:

MAPPING LOAD DISTINCT

     TERRITORY_CODE ,

     RESPONSIBLE_NAME

FROM [lib://QS_Projects_BA/10_Standard_Data/ALL/10_Shared/10_QVD/20_DIM/D_TERRITORY.qvd]

(qvd);

Map_TERRITORY:

Mapping LOAD Distinct

    TERRITORY_CODE,

   TERRITORY

FROM [lib://QS_Projects_BA/10_Standard_Data/ALL/10_Shared/10_QVD/20_DIM/D_TERRITORY.qvd]

(qvd);

MAP_SALES_GROUP:

MAPPING LOAD DISTINCT

     TERRITORY_CODE ,

     TERR_SALES_GROUP

FROM [lib://QS_Projects_BA/10_Standard_Data/ALL/10_Shared/10_QVD/20_DIM/D_TERRITORY.qvd]

(qvd);

MAP_COMMODITY:

MAPPING LOAD DISTINCT

    JOB_REFERENCE,

    COMMODITY_CODE

FROM [lib://QS_Projects_BA/10_Standard_Data/ALL/10_Shared/10_QVD/20_DIM/D_BL_TO_COMMODITY.qvd]

(qvd);

Map_CARRIER:

Mapping LOAD Distinct

    CARRIER_ID as CARRIER_CODE,

    CARRIER

FROM [lib://QS_Projects_BA/10_Standard_Data/ALL/10_Shared/10_QVD/20_DIM/D_CARRIER.qvd]

(qvd);

tmp1_CCA_F_BOOKING_COMMERCIAL_LINE:

LOAD

    key_CSA,

    key_award,

    key_bp,

    key_shipper,

    key_consignee,

    key_notify,

    key_nac,

    key_contract_holder,

    key_bkg_party,

    key_contract,

    key_agency,

    key_trade,

    key_POL,

    key_POD,

    key_LEG_POL,

    key_LEG_POD,

    key_POO,

    key_ROO,

    key_FPD,

    key_ROD,

    key_origin,

    key_destination,

    key_leg_voyage,

    key_voyage,

    LEG_DATE_TYPE,

//     LEG_POL_COUNTRY,

//     LEG_POD_COUNTRY,

//     LONG_LEG_FLAG,

//     LEG_VOYAGE_BOUND_ISSUE_FLAG,

//     CSA_ID,

     Source,

    JOB_REFERENCE,

//     SNAPSHOT_FLAG,

//     SNAP_DATE,

    DateNum,

//     DateNum_DIS,

//    WTD,

//     SALES_ORG_CODE,

//     BOOKING_OFFICE,

//     TERRITORY_SALES_OFFICE,

//     BOOKING_STATUS,

//     MYPRICE_FLAG,

    SALES_GROUP_CODE,

//     SALES_OFFICE_CODE,

//     AGENCY_CODE,

//     BULLET,

     COMMODITY_GROUP,

     COMMODITY,

//     ORION_COMMODITY,

//     PAYMENT_METHOD_CODE,

//     PAYMENT_METHOD,

    SHIPPING_TYPE,

    EQUIPMENT_SIZE_TYPE,

    CARGO_TYPE_CODE,

    CARGO_TYPE,

//     SHIPMENT_CATEGORY,

     FULL_EMPTY_CODE,

     FULL_EMPTY,

//     CONTRIBUTION_RECON_FLAG,

//     PRICING_GROUP,

    POL_COUNTRY,

   POD_COUNTRY,

    ORIGIN_COUNTRY,

    ORIGIN_COUNTRY_CODE,

    DESTINATION_COUNTRY,

    DESTINATION_COUNTRY_CODE,

//     SALES_OFFICE_COUNTRY_CODE,

//     BOOKING_OFFICE_COUNTRY_CODE,

    //BOOKING_TYPE,

     ACTUAL_CV_TEU_LSTS,

     //ACTUAL_CV_TEU,

     ACTUAL_CV_LSTS,

     ACTUAL_CV_BOX,

     ACTUAL_CV_WEIGHT,

//     TARGET_CV_TEU,

    ACTUAL_CV_NET_CONTRIBUTION,

//     TARGET_CV_NET_CONTRIBUTION,

//     POTENTIAL_MARKET_TEU,

//     TARGET_MARKET_TEU,

//     SALES_ORGANIZATION,

    FINANCIAL_VOYAGE_SERVICE

     , POL.MAIN_BUSINESS_ZONE,

     POD.MAIN_BUSINESS_ZONE

//     CIF_FOB,

//     GA_SEGMENT

FROM [lib://QS_Projects_BA/10_Standard_Data/ALL/10_Shared/10_QVD/40_DTM/30_FACTS/DTM_COMMERCIAL_LINE_Facts.qvd]

(qvd);

RAW_CLGCV06:

LEFT Join (tmp1_CCA_F_BOOKING_COMMERCIAL_LINE)LOAD

                CLGCBNO AS JOB_REFERENCE ,

     CLGCCARR AS CARRIER_CODE,

                "0CRM_TR" AS TERRITORY_CODE

FROM

                [lib://QS_Projects_BA/10_Standard_Data/ALL/10_Shared/10_QVD/10_RAW/10_SAP/CLGCV06.qvd]

(qvd);

 tmp1.2_CCA_F_BOOKING_COMMERCIAL_LINE:

NoConcatenate LOAD

key_CSA,

    key_award,

    key_bp,

    key_shipper,

    key_consignee,

    key_notify,

    key_nac,

    key_contract_holder,

    key_bkg_party,

    key_contract,

    key_agency,

    key_trade,

    key_POL,

    key_POD,

    key_LEG_POL,

    key_LEG_POD,

    key_POO,

    key_ROO,

    key_FPD,

    key_ROD,

    key_origin,

    key_destination,

    key_leg_voyage,

    key_voyage,

    LEG_DATE_TYPE,

    JOB_REFERENCE,

    DateNum,

  NUM(YEAR(DateNum), '0000') & NUM(MONTH(DateNum), '00')AS MONTH,

   NUM(Year(DateNum), '0000') & NUM(Ceil(Month(DateNum)/3), '00') AS QUARTER,

  NUM(YEAR(DateNum), '0000') & NUM(WEEK(DateNum), '00') AS WEEK,

  NUM(YEAR(DateNum), '0000')  AS YEAR,

    SALES_GROUP_CODE,

     COMMODITY_GROUP,

     COMMODITY,

    SHIPPING_TYPE,

    EQUIPMENT_SIZE_TYPE,

    CARGO_TYPE_CODE,

    CARGO_TYPE,

     FULL_EMPTY_CODE,

     FULL_EMPTY,

    POL_COUNTRY,

    POD_COUNTRY,

    ORIGIN_COUNTRY,

    ORIGIN_COUNTRY_CODE,

    DESTINATION_COUNTRY,

    DESTINATION_COUNTRY_CODE,

     ACTUAL_CV_TEU_LSTS,

     ACTUAL_CV_LSTS,

     ACTUAL_CV_BOX,

     ACTUAL_CV_WEIGHT,

    ACTUAL_CV_NET_CONTRIBUTION,

    FINANCIAL_VOYAGE_SERVICE

     , POL.MAIN_BUSINESS_ZONE,

     POD.MAIN_BUSINESS_ZONE,

   ApplyMap('MAP_SALES_REP',TERRITORY_CODE , ' NOT FOUND') AS SALES_REP,

     ApplyMap('Map_TERRITORY',TERRITORY_CODE , ' NOT FOUND') AS SALES_TERRITORY,

      ApplyMap('Map_CARRIER',CARRIER_CODE, ' NOT FOUND') AS CARRIER_CODE,

      ApplyMap('MAP_SALES_GROUP',TERRITORY_CODE, ' NOT FOUND') AS SALES_GROUP,

       ApplyMap('MAP_COMMODITY',JOB_REFERENCE, ' NOT FOUND') AS COMMODITY_CODE

       , IF (((LEN(TRIM(ORIGIN_COUNTRY_CODE)) = 0 OR ORIGIN_COUNTRY_CODE='NULL' OR ORIGIN_COUNTRY_CODE='-') AND (POL_COUNTRY = 'UNITED STATES' OR POL_COUNTRY = 'CANADA')) OR ((ORIGIN_COUNTRY_CODE = 'US' OR ORIGIN_COUNTRY_CODE = 'CA') AND (POL_COUNTRY = 'UNITED STATES' OR POL_COUNTRY = 'CANADA')), 'EXPORT'

                , IF ((((LEN(TRIM(DESTINATION_COUNTRY_CODE)) = 0 OR DESTINATION_COUNTRY_CODE='NULL' OR DESTINATION_COUNTRY_CODE='-') AND (POD_COUNTRY = 'UNITED STATES' OR POD_COUNTRY = 'CANADA') ) OR ((DESTINATION_COUNTRY_CODE = 'US' OR DESTINATION_COUNTRY_CODE = 'CA') AND (POD_COUNTRY = 'UNITED STATES' OR POD_COUNTRY = 'CANADA')) AND (POL_COUNTRY <> 'UNITED STATES' AND POL_COUNTRY <> 'CANADA')), 'IMPORT')) AS DIRECTION 

 Resident tmp1_CCA_F_BOOKING_COMMERCIAL_LINE;

  Drop Table tmp1_CCA_F_BOOKING_COMMERCIAL_LINE;

[COMMODITY_GROUPINGS]:

left Join (tmp1.2_CCA_F_BOOKING_COMMERCIAL_LINE)LOAD

    TRIM(COMMODITY_CD) AS COMMODITY_CODE,

    EXP_KEY_COMMODITY_TXT,

    EXP_SUB_COMMODITY_TXT,

    EXP_KEY_REEFER_TXT,

    IMP_KEY_COMMODITY_TXT,

    IMP_SUB_COMMODITY_TXT,

    IMP_KEY_REEFER_TXT

FROM [lib://QS_Projects_BA/10_Standard_Data/ALL/10_Shared/10_QVD/COMMODITY_GROUPS_TBL.qvd]

(qvd);

tmp2_CCA_F_BOOKING_COMMERCIAL_LINE:

NoConcatenate LOAD

*

, IF (DIRECTION = 'IMPORT', IF(CARGO_TYPE_CODE = 'RFR', 'REEFER', IF (CARGO_TYPE_CODE = 'OOG', 'OOG', IMP_KEY_COMMODITY_TXT))

               , IF (DIRECTION = 'EXPORT', IF(CARGO_TYPE_CODE = 'RFR', 'REEFER', IF (CARGO_TYPE_CODE = 'OOG', 'OOG', EXP_KEY_COMMODITY_TXT)), NULL())) AS KEY_COMMODITY

                , IF (DIRECTION = 'IMPORT', IF(CARGO_TYPE_CODE = 'RFR', IMP_KEY_REEFER_TXT, IMP_SUB_COMMODITY_TXT)

               , IF (DIRECTION = 'EXPORT', IF(CARGO_TYPE_CODE = 'RFR', EXP_KEY_REEFER_TXT, EXP_SUB_COMMODITY_TXT), NULL())) AS SUB_KEY_COMMODITY

Resident tmp1.2_CCA_F_BOOKING_COMMERCIAL_LINE

WHERE

                MATCH(DIRECTION, 'IMPORT', 'EXPORT')

                AND

                LEG_DATE_TYPE = 'Week at LAP';

Store tmp2_CCA_F_BOOKING_COMMERCIAL_LINE INTO [lib://QS_Projects_BA/10_Standard_Data/ALL/10_Shared/10_QVD/tmp2_CCA_F_BOOKING_COMMERCIAL_LINE.qvd]

(qvd);

Drop table tmp1.2_CCA_F_BOOKING_COMMERCIAL_LINE;

Drop table tmp2_CCA_F_BOOKING_COMMERCIAL_LINE; 

Hi All , 

Please refer my data screenshot below after loading the file  . now if you look  for the field FeatureID i have Empty and for the field Feature there is just a dash, 

How can I replace the dash (-) under Feature column by looking at the TeamID . Thanks

Thanks in Advance

I am bringing data using two views in the data load script like below. When I run the views in oracle directly each view is taking around 1 minute. But the data load using Qlik is taking around 4-5 minutes. I am not bringing a lot of data in to the memory also ( around 3 hundred thousand)

TABLE A:

VIEW 1 :Select * from A,B,C,D where (A.ID=B.ID AND B.KEY=C.KEY AND D.ID1 =C.ID1)

TABLE B:

VIEW 2

Hi Experts,

I have a Excel Report with two straight tables.

I want to dynamically remove a table based on the filter selected.

Ex: Excel Report Template.

I have two publish task.

Task1: No filters 

Desired output :

Task2: Product Filter

Desired Output:

Basically I just wanted to get rid of Table 2 from the excel Template.

Is it possible to achieve this using a single report or do i need to create separate report ?

Your help is highly appreciated.

Thanks

Syed

Hi 

I have the following scenario:

i have a fact table which has, the following fields:

ItemCode, SupplierCode, DocDate, PurchDocNum, ObjType (has 2 types: 103 and 101 for each PurchDocNum)

my mission is to Flag each combination of: ItemCode&SupplierCode  in PurchDocNum when ObjType =103 

that have combination on ItemCode&SupplierCode when ObjType =101  in a date greater than the date is when  ObjType =103

i atteached csv copy of the data

thank you!

Hi,

I am new in QlikView and I have the following situation. I have a table from a load with multiple fields and I want to count how many IDs are using the same Product ID in each month. So I need to create a new column named: "check offers" as per below table:

So far my script looks like this:

Table1:
LOAD [D],
[ID Name],
[ID Year Month],
[Product ID],
[Offer Type],
[Month],
[No of products]

FROM
[\\xxxxxl\xxxxx\xxxxx\xxxxx\products.qvd]
(qvd);

Table2:

LOAD
[Product ID] as [Product],
[Month] as [Month_date],
count([Products ID]) as [check offer]

RESIDENT TB1
GROUP BY
[Month],
[Product ID];

But this doesn't bring me the result I want.

Can you help with a solution? I want to keep all columns from my load + create the new column by 2 condition (to count how many IDs are using the same Product ID in each month).

Thank you

Hi

I have a calculated field (CCY_SETID) in my load statement. Now I need to have the total count of (CCY_SETID) . Please help

TLMMO:

LOAD
SET_ID,
IF(Wildmatch(SET_ID,'*GDS*')=1,NULL(),SET_ID) AS CCY_SETID

FROM
$(file)

Hola buenas tardes,

Agradezco de antemano su atención, mi consulta es de "suma de acumulada en pivot".

Tengo una tabla con las siguientes columnas: Concesionario_atencion, Año_lanza, Mes_lanza y Cantidad.

Deseo obtener 2 Columnas Acumuladas, "Acumulado" y "Acumulado Total"

En la columna Acumulada; se busca la suma acumulada de "Cantidad" por "Concesionario_atencion", "Año_lanza", "Mes_lanza", reiniciando en cada año.

En el periodo Oct-2015, el valor es incorrecta de la columna "Acumulado" lo correcto es la suma de: 539 + 519 =1058, pero muestra 1077 (539 + 519 + 19).

La expresion empleada es:

= aggr( rangesum(above( count({<Año_lanza>}[Fechalanzamiento]),0,rowno(TOTAL))),Concesionario_atencion,Año_lanza,Mes_lanza)

Y la columna Acumulada Total debe mostrar la suma de cada fila y reiniciar al ser un Concesionario distinto "Concesionario_atencion"

Expresion empleada:

= aggr( rangesum(above( TOTAL count({<Concesionario_atencion>}[Fechalanzamiento]),0,rowno())),Concesionario_atencion,Año_lanza,Mes_lanza)

Gracias.

Si necesita mayor aclaració no dude en contactarme.

Saludos