topic Re: How to use nested select statement in report in QlikView

How to use nested select statement in report

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

i want to combine two select statement in my report

following are the two nested statements

please help me in writting the corresponding code in script

These are two select statement

SQL SELECT WH.GoodsSender as NCode, SD.InvoiceNumber, SD.SalesDate,SD.ItemNumber,

SD.SaleQuantity, SD.NetValue,SD.MRPValue,SD.TaxAmount

FROM SAP_Link.dbo.Sales_DataNonETP_BIReports SD

INNER JOIN ETPEASV55.dbo.warehouse_sap_bireport WH

ON SD.CustomerId = WH.Warehouse

where SD.Doc_Type = 'ZIKE' and SD.SalesDate >= 20130101 and SD.SalesDate <= '$(currentdate)'

select warehouse,warehousedescription,goodssender from warehouse Where GoodsSender<>'NA' and GoodsSender like 'N%'

union all

select warehouse,warehousedescription,goodssender from ETPPOS.ETPEAS.dbo.Warehouse_SAP Where ISnonETP='Y' and GoodsSender<>'NA' and GoodsSender like 'N%'

Re: How to use nested select statement in report

prma7799 — Thu, 02 Nov 2017 05:18:01 GMT

Are you trying to this in SQL or in Qlikview ??

if you want to this in Qlikveiw try to take data into qvd format then do the calculation.

Re: How to use nested select statement in report

prma7799 — Thu, 02 Nov 2017 05:18:42 GMT

Could you please explain what is the relation between to tables? which are the common column between two tables

Re: How to use nested select statement in report

Anonymous — Thu, 02 Nov 2017 05:31:14 GMT

SQL SELECT WH.GoodsSender as NCode, SD.InvoiceNumber, SD.SalesDate,SD.ItemNumber,

SD.SaleQuantity, SD.NetValue,SD.MRPValue,SD.TaxAmount

FROM SAP_Link.dbo.Sales_DataNonETP_BIReports SD

INNER JOIN ETPEASV55.dbo.warehouse_sap_bireport WH

ON SD.CustomerId = WH.Warehouse

where SD.Doc_Type = 'ZIKE' and SD.SalesDate >= 20130101 and SD.SalesDate <= '$(currentdate)'

i Want this condition with the above one

where warehouse in(select distinct warehouse from warehouse_sap where goadssender like '%N'

warehouse and goads sender are the common columns between two feilds

Re: How to use nested select statement in report

Anonymous — Thu, 02 Nov 2017 05:31:57 GMT

I want to actually insert the second condition with the first one

how to do that ?

Re: How to use nested select statement in report

Anonymous — Thu, 02 Nov 2017 05:33:44 GMT

Re: How to use nested select statement in report

Anonymous — Thu, 02 Nov 2017 05:35:00 GMT

now i want to add this condition also where warehouse in (select distinct warehouse from warehouse_sap where goadssender like '%n'

Re: How to use nested select statement in report

prma7799 — Thu, 02 Nov 2017 05:50:39 GMT

If ETPEASV55.dbo.werehouse _sap_bireport same like warehouse_sap this table then you can try like below

where wildmatch(goadssender,'n*' )

or goads sender are the common columns between two table then

from ETPEASV55.dbo.werehouse _sap_bireport WH

left join

warehouse_sap WS

on WH.goadssender = WS.goadssender

where WS.goadssender like '%n';

Re: How to use nested select statement in report

Anonymous — Thu, 02 Nov 2017 06:02:56 GMT

please refer the following image

Re: How to use nested select statement in report

Anonymous — Thu, 02 Nov 2017 06:04:31 GMT

these are two tables

SAP_Link.dbo.Sales_DataNonETP_BIReports SD and

ETPEASV55.dbo.warehouse_sap_bireport WH

goodssender is the common feild in both

Re: How to use nested select statement in report

prma7799 — Thu, 02 Nov 2017 06:04:49 GMT

Please share script or sample app.

Re: How to use nested select statement in report

Anonymous — Thu, 02 Nov 2017 06:25:48 GMT

master tables

Let currentdate =DATE(Today(1)-1,'YYYYMMDD');

Let Curr_Year = Year(Today());

Let Prev_Year = (Year(Today())-1);

//POS_Master:

//LOAD ETPCode,

// [inv no] ,

// Sold_Date ,

// Sold_Month ,

// Sold_Year ,

// itemnumber ,

// [Season Code] ,

// quantity_sold ,

// [sales price] ,

// [inv value] ,

// [Inv val Bucket],

// [tax amount],

// [return no],

// quantity_returns

//FROM

//(qvd)

//where Sold_Date <= MakeDate(2013,01,01) and NOT WildMatch(ETPCode,'*ETP*','N*N','N*');

//Last_Updated_Date:

//load

//max(Sold_Date) as Max_Date_Sales

//RESIDENT POS_Master;

//Let Last_updated_Sales_date =

// DATE(peek('Max_Date_Sales',0,'Last_updated_Sales_date')+1,'YYYYMMDD');

/*Store_Mapping:

LOAD * FROM

(qvd);

Store_SAP_Code:

mapping LOAD SAPCode,

[Store Name]

FROM

(qvd);

Store_Mapping:

LOAD SAPCode,

ApplyMap ('Store_SAP_Code',SAPCode,'') as [Store Name],

ETPCode,

UCB_Data_check,

[Distribution Channel],

[Bill To Name],

[Ship To Name],

Tier,

[Bill To State],

Store_ranking,

[Sales Group],

[Ship to State],

Brand_06,

Brand_0Y,

Condition_group4,

Country,

CustomerClassific,

District,

Group,

Interior_Designer,

[No of Floors],

No_of_Shop_Window,

[Operating Cost],

Outlet_store,

PostalCode,

Region,

[Rent (2016 %)],

[Rent (2016 A)],

[Rent (2017 A)],

[Rent(2017 %)],

[Shop Opening Year],

[Shop Postion],

[Stock Room Surface],

[Store Area],

Street,

[Active flag],

City

FROM

(qvd);

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Data Source=192.168.1.108;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=QLIKVIEW;Use Encryption for Data=False;Tag with column collation when possible=False];

Material_Master:

LOAD MATERIAL,

COLOR,

SIZE,

MATERIAL&COLOR&SIZE as itemnumber,

[Size Group],

Description,

COMPOSITION as Promotional_1,

BRANDCD,

BRAND,

SEASON,

SEASONDESC,

THEME,

DELYCODE,

GENDER,

IF(GENDER ='LADIES', 'WOMEN', IF(GENDER='MENS','MENS', IF(GENDER='BOYS' or GENDER='GIRLS' or GENDER='BABY','KIDS', GENDER))) as Gender_Revised,

ProductHierarchy,

MaterialGroup,

[Core/Fashion],

FIT,

PATTERN,

[Key/NonKey],

RANGE,

ORIGIN,

[Top/Bottom],

SLEEVE,

NECK,

WEATHER,

OCCASSION,

CustomDesc,

MaterialGrp4,

MGRP4DESC,

EAN,

ALTEAN,

PurchaseGroup,

PurchaseGroupDesc,

CATEGORY,

SUBCAT,

BASECOMP,

MaterialType,

ValuationClass,

COST,

MRP as mrp_INR,

if (MRP <1000, '<1000', if (MRP <2000, '1000-2000',if (MRP <3000, '2000-3000',if (MRP <4000, '3000-4000',if (MRP <5000, '4000-5000',if (MRP <6000, '5000-6000', '>6000')))))) as [MRP Bucket]

FROM

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

Temp:

load ETPCode

RESIDENT Store_Mapping;

MRP_temp:

mapping LOAD itemnumber,

mrp_INR

Resident Material_Master;

SAPCODE_ETPCodeMapping:

Mapping load SAPCode,

ETPCode

Resident Store_Mapping;

Images:

bundle LOAD Material,

ImageLocation

FROM

(ooxml, embedded labels, table is Sheet1);

script uner te tab incremental load

Increment_sales:

concatenate(POS_Master_final)

Load

Warehouse as ETPCode,

InvoiceNumber as [inv no],

Date#(CreateDate, 'YYYYMMDD') as Sold_Date,

month(Date#(CreateDate, 'YYYYMMDD')) as Sold_Month,

year(Date#(CreateDate, 'YYYYMMDD')) as Sold_Year,

replace(ItemNumber,' ','') as itemnumber,

left(ItemNumber,3) as [Season Code],

InvoiceQuantity as quantity_sold,

SalesPrice as [sales price],

LocalAmount as [inv value],

if ( LocalAmount <500, '<500', if ( LocalAmount <800, '500-799',if ( LocalAmount <1200, '800-1199',if ( LocalAmount <1600,'1200-1599',if ( LocalAmount <2500,'1600-2499',if ( LocalAmount <3500,'2500-3499',if ( LocalAmount <5000,'3500-4999','>=5000'))))))) as [Inv val Bucket],

VatAmount as [tax amount];

SQL SELECT *

FROM ETPEASV55.dbo.CashOrderTrn

WHERE InvoiceType = '31' and (CreateDate >= 20130101 and CreateDate <= '$(currentdate)') ;

Increment_Returns:

concatenate(POS_Master_final)

LOAD Warehouse as ETPCode,

SalesReturnNumber as [return no],

Date#(CreateDate, 'YYYYMMDD') as Sold_Date,

month(Date#(CreateDate, 'YYYYMMDD')) as Sold_Month,

year(Date#(CreateDate, 'YYYYMMDD')) as Sold_Year,

replace(ReturnItemNumber,' ','') as itemnumber,

left(ReturnItemNumber,3) as [Season Code],

ReturnQuantity as quantity_returns,

SalesPrice as [sales price],

-LocalAmount as [inv value],

SQL SELECT *

FROM ETPEASV55.dbo.SalesReturnTrn

WHERE InvoiceType = '31' and (CreateDate >= 20130101 and CreateDate <= '$(currentdate)');

store POS_Master_final into ;

Incremental_Non_Etp_Sales:

Concatenate(POS_Master_final)

Load

ApplyMap('SAPCODE_ETPCodeMapping',NCode,'') as ETPCode,

InvoiceNumber as [inv no],

Date#(SalesDate, 'YYYYMMDD') as Sold_Date,

month(Date#(SalesDate, 'YYYYMMDD')) as Sold_Month,

year(Date#(SalesDate, 'YYYYMMDD')) as Sold_Year,

replace(ItemNumber,' ','') as itemnumber,

left(ItemNumber,3) as [Season Code],

//'N_ETP SALE' as SaleType,

SaleQuantity as quantity_sold,

NetValue as [inv value],

MRPValue as [sales price],

if ( NetValue <500, '<500', if ( NetValue <800, '500-799',if ( NetValue <1200, '800-1199',if ( NetValue <1600,'1200-1599',if ( NetValue <2500,'1600-2499',if ( NetValue <3500,'2500-3499',if ( NetValue <5000,'3500-4999','>=5000'))))))) as [Inv val Bucket],

TaxAmount as [tax amount];

SQL SELECT WH.GoodsSender as NCode, SD.InvoiceNumber, SD.SalesDate,SD.ItemNumber,

SD.SaleQuantity, SD.NetValue,SD.MRPValue,SD.TaxAmount

FROM SAP_Link.dbo.Sales_DataNonETP_BIReports SD

INNER JOIN ETPEASV55.dbo.warehouse_sap_bireport WH

ON SD.CustomerId = WH.Warehouse

where SD.Doc_Type = 'ZIKE' and SD.SalesDate >= 20130101 and SD.SalesDate <= '$(currentdate)'

;

Incremental_Non_Etp_Returns:

Concatenate(POS_Master_final)

Load

ApplyMap('SAPCODE_ETPCodeMapping',NCode,'') as ETPCode,

InvoiceNumber as [return no],

Date#(SalesDate, 'YYYYMMDD') as Sold_Date,

month(Date#(SalesDate, 'YYYYMMDD')) as Sold_Month,

year(Date#(SalesDate, 'YYYYMMDD')) as Sold_Year,

replace(ItemNumber,' ','') as itemnumber,

left(ItemNumber,3) as [Season Code],

//'N_ETP SALE' as SaleType,

SaleQuantity as quantity_returns,

-NetValue as [inv value],

MRPValue as [sales price],

-TaxAmount as [tax amount];

SQl Select WH.GoodsSender as NCode, SD.InvoiceNumber, SD.SalesDate,SD.ItemNumber,

SD.SaleQuantity, SD.NetValue,SD.MRPValue,SD.TaxAmount

FROM SAP_Link.dbo.Sales_DataNonETP_BIReports SD

INNER JOIN ETPEASV55.dbo.warehouse_sap_bireport WH

ON SD.CustomerId=WH.Warehouse

where SD.Doc_Type = 'ZIKR' and SD.SalesDate >= 20130101 and SD.SalesDate <= '$(currentdate)' ;

Increment_Receiving_temp1:

LOAD

TOWarehouse as ETPCode,

replace(ItemNumber,' ','') as itemnumber,

left(ItemNumber,3) as [Season Code],

Date#(CurrTransactionDate, 'YYYYMMDD') as Received_Date,

ReceivingNumber as [rec number],

ReceivedQuantity as quantity_received;

SQL SELECT *

FROM ETPEASV55.dbo.DOLine

WHERE(CurrTransactionDate >= 20130101 and CurrTransactionDate <= '$(currentdate)') ;

Incremental_Receiving_Non_Etp:

Concatenate (Increment_Receiving_temp1)

Load

ApplyMap('SAPCODE_ETPCodeMapping',NCode,'') as ETPCode,

Date#(SalesDate, 'YYYYMMDD') as Received_Date,

replace(ItemNumber,' ','') as itemnumber,

left(ItemNumber,3) as [Season Code],

InvoiceNumber as [rec number],

SaleQuantity as quantity_received

;

SQl Select WH.GoodsSender as NCode, SD.InvoiceNumber, SD.SalesDate,SD.ItemNumber,

SD.SaleQuantity

FROM SAP_Link.dbo.Sales_DataNonETP_BIReports SD

INNER JOIN ETPEASV55.dbo.warehouse_sap_bireport WH

ON SD.CustomerId=WH.Warehouse

where SD.Doc_Type = 'GR' and SD.SalesDate <= '$(currentdate)' and SD.SalesDate >= 20130101 ;

Increment_Receiving_temp2:

Concatenate (Increment_Receiving_temp1)

Load

ApplyMap('SAPCODE_ETPCodeMapping',NCode,'') as ETPCode,

Date#(SalesDate, 'YYYYMMDD') as Received_Date,

replace(ItemNumber,' ','') as itemnumber,

left(ItemNumber,3) as [Season Code],

InvoiceNumber as [rec number],

-SaleQuantity as quantity_received

;

SQl Select WH.GoodsSender as NCode, SD.InvoiceNumber, SD.SalesDate,SD.ItemNumber,

SD.SaleQuantity

FROM SAP_Link.dbo.Sales_DataNonETP_BIReports SD

INNER JOIN ETPEASV55.dbo.warehouse_sap_bireport WH

ON SD.CustomerId=WH.Warehouse

where SD.Doc_Type = 'STN' and SD.SalesDate <= '$(currentdate)' and SD.SalesDate >= 20130101 ;

Increment_Receiving_final:

left keep (Temp)

load

ETPCode,

itemnumber,

[Season Code],

quantity_received,

Received_Date

Resident Increment_Receiving_temp1;

drop table Increment_Receiving_temp1;

Increment_Receiving_data:

Concatenate(POS_Master_final)

load *

Resident Increment_Receiving_final;

drop table Increment_Receiving_final;

drop table Temp;

Re: How to use nested select statement in report

Anonymous — Thu, 02 Nov 2017 06:28:28 GMT

following data model

Re: How to use nested select statement in report

prma7799 — Thu, 02 Nov 2017 06:33:44 GMT

kindly provide breif details of below table

select distinct warehouse from warehouse_sap where goadssender like '%n'