topic Re: How to apply join? in QlikView

How to apply join?

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

I want to apply join in these above two tables. How to apply? and which join should i use?

Increment_Sales:

Load
Warehouse as SAPCode
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 ETPEAS.dbo.CashOrderTrn
WHERE InvoiceType = '31' and (CreateDate >= 20130101 and CreateDate <= '$(currentdate)') ;

Increment_Returns:
LOAD
Warehouse as SAPCode,
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],
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];
SQL SELECT *
FROM ETPEAS.dbo.SalesReturnTrn
WHERE InvoiceType = '31' and (CreateDate >= 20130101 and CreateDate <= '$(currentdate)') ;

Re: How to apply join?

prma7799 — Wed, 14 Feb 2018 07:41:56 GMT

why you want to join this

you can Concatenate this with Flag...

Like this

Load A,B,C,......, 'Sales' as Flag

from TableA;

Concatenate

Load A,B,C,....., 'Return' as Flag

from TableB;

Re: How to apply join?

shiveshsingh — Wed, 14 Feb 2018 07:49:09 GMT

what is your expected output? Data from both the tables? Concatenation will be a good option

Re: How to apply join?

Anonymous — Wed, 14 Feb 2018 07:50:06 GMT

i want to join this because I have more tables with this to join like below:-

Incremental_Non_Etp_Sales:

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 ETPEAS.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:

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],
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 ETPEAS.dbo.warehouse_sap_bireport WH
ON SD.CustomerId=WH.Warehouse
where SD.Doc_Type = 'ZIKR' and SD.SalesDate >= 20130101 and SD.SalesDate <= '$(currentdate)' ;

Re: How to apply join?

shiveshsingh — Wed, 14 Feb 2018 07:53:01 GMT

You can concatenate by giving the flag and then use that flag in set analysis for calculation

Re: How to apply join?

prma7799 — Wed, 14 Feb 2018 07:56:25 GMT

Try to concatenate with flag concept to all your Tables and see. Join is not good idea ...

Please check this

Understanding Join, Keep and Concatenate

http://qlikviewcookbook.com/2009/11/understanding-join-and-concatenate/

Re: How to apply join?

Anonymous — Wed, 14 Feb 2018 08:20:00 GMT

I have all theses below tables. i want to merge all the tables (Increment_Sales, Increment_Returns,Incremental_Non_Etp_Sales,Incremental_Non_Etp_Returns,)

I want other tables(Increment_Receiving_temp1, Incremental_Receiving_Non_Etp,Increment_Receiving_temp2,Incremental_Receiving_Non_Etp_2) to be separated from following above tables (Increment_Sales, Increment_Returns,Incremental_Non_Etp_Sales,Incremental_Non_Etp_Returns,)

Load
Warehouse as SAPCode,
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 ETPEAS.dbo.CashOrderTrn
WHERE InvoiceType = '31' and (CreateDate >= 20130101 and CreateDate <= '$(currentdate)') ;

Increment_Returns:
LOAD
Warehouse as SAPCode,
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],
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];
SQL SELECT *
FROM ETPEAS.dbo.SalesReturnTrn
WHERE InvoiceType = '31' and (CreateDate >= 20130101 and CreateDate <= '$(currentdate)') ;

//store POS_Master_final into ;

Incremental_Non_Etp_Sales:
Load
Warehouse as SAPCode,
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 ETPEAS.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:
Load
Warehouse as SAPCode,
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],
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 ETPEAS.dbo.warehouse_sap_bireport WH
ON SD.CustomerId=WH.Warehouse
where SD.Doc_Type = 'ZIKR' and SD.SalesDate >= 20130101 and SD.SalesDate <= '$(currentdate)' ;

I want below tables (all) should be kept separate from above tables

Increment_Receiving_temp1:
LOAD
Warehouse as SAPCode,
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 ETPEAS.dbo.DOLine
WHERE(CurrTransactionDate >= 20130101 and CurrTransactionDate <= '$(currentdate)') ;

Incremental_Receiving_Non_Etp:

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 ETPEAS.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:

LOAD
Warehouse as ETPCode,
replace(ItemNumber,' ','') as itemnumber,
left(ItemNumber,3) as [Season Code],
Date#(CurrTransactionDate, 'YYYYMMDD') as Received_Date,
-ReceivedQuantity as quantity_received;
SQL SELECT *
FROM ETPEAS.dbo.DOLine
WHERE(CurrTransactionDate >= 20130101 and CurrTransactionDate <= '$(currentdate)') ;

Incremental_Receiving_Non_Etp_2:

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 ETPEAS.dbo.warehouse_sap_bireport WH
ON SD.CustomerId=WH.Warehouse
where SD.Doc_Type = 'STN' and SD.SalesDate <= '$(currentdate)' and SD.SalesDate >= 20130101;