Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

Calendar issue... How to get the dates of two tables to work together

I have the code below.

I think have  list boxes for Year , another for Day,  another for Quarter , and another for Sales Month.

I want to do is select a Year, Day Quarter or SalesMonth(or some Month) and get both the Receipts and Sales for that time period.

The Sales work just fine as it is joined to the Sales Calendar. But how do I get it so that the Receipts will also show for that same time period when I click on one of the List Box values?

For my CALENDARS

QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);

Temp:
Load
                min(DayEndDate) as minDate,
                max(DayEndDate) as maxDate
Resident Sales_Header;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;

TempCalendar:
LOAD
                $(varMinDate) + Iterno()-1 As Num,
                Date($(varMinDate) + IterNo() - 1) as TempDate
                AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
               
SalesCalendar:
Load
                //TempDate AS InvoicePrintDate,
                TempDate AS DayEndDate,
                week(TempDate) As Week,
                Year(TempDate) As Year,
                Month(TempDate) As SalesMonth,
                Day(TempDate) As Day,
                Year2Date(TempDate)*-1 as CurYTDFlag,
                Year2Date(TempDate)*-1 as LastYTDFlag,
                inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
                date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
                ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
                Week(TempDate) & '-' & Year(TempDate) as WeekYear,
                WeekDay(TempDate) as WeekDay,
                DayNumberOfYear(TempDate) as DNOY
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;


Temp2:
Load
                min(ReceiptDate) as minDate,
                max(ReceiptDate) as maxDate
Resident Inventory_Receipts;

Let varMinDate = Num(Peek('minDate', 0, 'Temp2'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp2'));
DROP Table Temp2;

TempCalendar2:
LOAD
                $(varMinDate) + Iterno()-1 As Num,
                Date($(varMinDate) + IterNo() - 1) as TempDate2
                AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
               
ReceiptCalendar:
Load
                TempDate2 AS ReceiptDate,
                week(TempDate2) As ReceiptWeek,
                Year(TempDate2) As ReceiptYear,
                Month(TempDate2) As ReceiptMonth,
                Day(TempDate2) As ReceiptDay,
                Year2Date(TempDate2)*-1 as ReceiptCurYTDFlag,
                Year2Date(TempDate2)*-1 as ReceiptLastYTDFlag,
                inyear(TempDate2, Monthstart($(varMaxDate)),-1) as ReceiptRC12,
                date(monthstart(TempDate2), 'MMM-YYYY') as ReceiptMonthYear,
                ApplyMap('QuartersMap', month(TempDate2), Null()) as ReceiptQuarter,
                Week(TempDate2) & '-' & Year(TempDate2) as ReceiptWeekYear,
                WeekDay(TempDate2) as ReceiptWeekDay,
                DayNumberOfYear(TempDate2) as ReceiptDNOY
Resident TempCalendar2
Order By TempDate2 ASC;
Drop Table TempCalendar2;

NOW HERE IS THE RECEIPT QVD

Inventory_Receipts:

LOAD

//  [Credit Allowed],   No Data

//    [Business Unit],  No Data

//    [Company Number],  In HSHED

     [Invoice Cost] as [Invoice Cost RCPT],

     [Drill Down Sequence No],

//    [Drop Ship Code (Y or N)],  In HSHED

     [Due Date Century] & [Due Date] as [Due Date RCPT],

     [Error Message(1)],

//    [Tax ID],  No Data

     [Group ID Number],

//    [GST Amount],  No Data

//    [GST Tax Percent],     No Data

     [History Seq Numb     er] as [History Sequence Number RCPT],

     [Invoice Value],

//     [Item Number],  In ITMST

//     Century,  In HSHED

     [Invoice Date] as [Invoice Date RCPT],

     [Lead Time Calc],

//     [Line Sequence No],

//     [Lot Charge Code],   In HSDET

     [Non-Inventory Flag],

     [Order Quantity] as [Received Order Quantity],

     [Override Code],

     [Override Cost(1)],

     [Posting Century],

     [GL Posting Date],

//    [Posting Flag],  No Data

     [Purchase Order Number] as [Received Purchase Order Number],

     [Rcpt Qty Stck U/M],

     [Receipt Date Century],

     [Received Complete Code],

     right(left([Receipt Date] , 4), 2) & '/' &

      right([Receipt Date], 2) & '/20' &

      left([Receipt Date], 2)

      as ReceiptDate,     //renamed to tie to Master Calendar

     right(left([Receipt Date] , 4), 2) as RCPTReceitpTMonth,

     [Receipt Date],

     [Receipt Quantity],

     [Receiver Number],

     [Total Receipt Weight],

//     [Record Number],   No Data

//     [Reserved Area],   No Data

//     [Substitute Item Number],  No Data

//     [Substitute Non-Stocked],  No Data

     [Validation Sequence Number],

     [Receipt Sequence Number],

     [Date/Time Stamp],

     Time,

     [Transaction Date Century],

     [Transaction Date],

//     [Tax Body],   In HSHED

     [Tax Drill Down Sequence No],

//     [Unit of Measure],  In HSDET

     [Rcpt Unit of Measure],

     [Update Average Cost],

//    [Update Inventory Code],   In ITMST

//     [Vendor GL Code],  No Data

//    [Vendor Number],

     [Voucher Company],

     [Voucher #],

     [Warehouse ID] as [ReceiptWarehouseID],

     [Warehouse ID] & [Item Number] as [WarehouseItemKey],

     [Work Station Id]

FROM

[\\CHP-QLIKVIEW\ProgramData\QlikTech\SourceDocuments\QVD\RCPT.qvd]

(qvd);

HERE IS THE SALES FILE

Sales_Header:

LOAD

  [Acknowledgement Print Date],

     [Acknowledge Print Time],

     [Acknowledge Print User],

     [Allocation Date Century],

     [Allocated Code],

     [Allocation Date],

     [Accounts Receivable Customer Amount],

     [Accounts Receivable Customer Number],

     [Amount Subject to Tax],

     [BILL OF LADING NUMBER Number],

     [Carrier Code],

//     [Consolidated Bill Code],   No Data

//     [Credit Card Authorization],  No Data

     [Cash Discount Amount],

     [Cash Discount Percent],

     [Consolidated Inv. Control #],

     [Consolidated Inv. Prt Flag],

     [Cancel Date Century],

     [Cancel Date],

     [Total Container Charge],

     [Contract Number],

     [Company Number],

     [Commission Percent - Rep Number One],

     [Commission Percent - Rep Number Two],

     [Commission Percent - Rep Number Three],

     [Price Discount Code],

     [Customer Price List],

     [Credit Card Number],

     [Complete Ship Code],

//     [Customer Class],   In CUSMS

//     [Customer Contract Code],  In CUSMS

   [Customer GL Code] as CustomerGLCODE,

     [Customer Number],

//     [Consignment Order Type],  No Data

     [Customer Purchase Order Number],

//     [Customer Sort Word],   In CUCMS

//     [Customer Subclass],   No Data

     [Due Date],

     Department,

     [Dayend Centry],

     [Dayend Date],

      right(left([Dayend Date] , 4), 2) & '/' &

      right([Dayend Date], 2) & '/20' &

      left([Dayend Date], 2)

      as DayEndDate,     //renamed to try and tie to Sales calendar

     [Deposit Amount],

     [Days in Process Centry],

     [Days in Process Date],

     [Days in Process Time],

     [Drop Ship Code (Y or N)],

//    [Driver ID],   No Data

     [Drop Ship Amount],

     [EDI Acknowldgement Century],

     [EDI Acknowldgement Date Sent],

     [EDI Acknowledgement Status],

     [EDI Acknowldgement Time Sent],

     [EDI ASN Century],

     [EDI ASN Date Sent],

     [Hold EDI ASN Y/N],

     [EDI ASN Status],

     [EDI ASN Time Sent],

     [Hold EDI Acknwldgmnts Y/N],

     [EDI Acknowledgements Y/N],

     [EDI Advance Ship notice Y/N],

//     [EDI Ack In-Use WS],   No Data

     [EDI Order Create Time],

     [Hold EDI Invoices Y/N],

     [EDI Invoices Y/N],

//     [EDI Invoice In-Use WS],   No Data

     [EDI Invoice Century],

     [EDI Invoice Date Sent],

     [EDI Invoice Status],

     [EDI Invoice Time Sent],

     [EDI Order Y or blank],

     [EDI ASN In-Use WS],

     [Email Acknowledgements Y/N],

     [Email Invoices Y/N],

     [Order Entry Date],

     Century,

     [Web Entry Date],

     [Entry Time],

     [Entry User],

     [Expiration Century],

     [Exempt Code],

     [Federal Excise Tax Code],

     [Tax Exempt Expiration Date],

     [Item Exempt Amount 1],

     [Item Exempt Amount 2],

     [Item Exempt Amount 3],

     [Tax Exempt Certificate Number],

     [Credit Card Expiration Date],

     [Federal Excise Amount],

     [1 Character Filler Field],

//     [4 Character Filler Field],  No Data

     [FOB Code],

     [Future Date],

     [Fax Acknowledgements Y/N],

     [Fax Invoices Y/N],

     [GST Amount],

     [GST Tax Percent],

     [GST Yes/No],

     [Hold Code],

     [History Sequence Number],

//     [Internal Vendor Number],

     [Invoice Amount],

     [Invoice Number],

     [Invoice Print Time],

     [Invoice Print User],

    // right(left([Invoice Print Date] , 6), 2) & '/' &

    //  right([Invoice Print Date], 2) & '/' &

    //  left([Invoice Print Date], 4)

    //  as InvoicePrintDate,     //renamed to tie to Sales Calendar CHANGED to use DAYEND DATE

     [Invoice Date],

     [Item Sales Amount],

//    Keycode,   No Data

     [Last Hold Code],

     [Last User ID],

     [Last User to Release this Order],

//     [Market Source],   No Data

     [Manual Released Future Order],

     [Net Date],

//     [Nick Name],

     [Number of Items],

     [Number of Messages],

     [Number of Special Charges],

     [Miscellaneous Note],

     [New Order Code],

     [All Items BackOrdered Flag],

     [Extended Order Cost],

     [Order Generation Number],

     [Order Number],

     [Order Reference Number],

     [Order Source],

     if([Order Source] = 'FL','FL' ,

     if([Order Source] = 'MW','MW' ,

     if([Order Source] = 'WC', 'WC' ,

     if([Order Source] = 'NE','NE' ,    

     if([Order Source] = 'JS','JS' ,

     if([Order Source] = 'OS','OS' ,

     if([Order Source] = 'BN','BN' ,

     if([Order Source] = 'NT','NT' ,

     if([Order Source] = 'SG','SG' ,

     if([Order Source] = 'JE','JS', [Customer GL Code]))))))))))  as SalesOffice ,

//     if( SalesOffice = 'FL','Florida-Export' ,

//     if(SalesOffice = 'MW','Midwest' ,

//     if(SalesOffice = 'WC', 'West Coast' ,

//     if(SalesOffice = 'NE','Northest' ,    

//     if(SalesOffice = 'JS','Jerrys Sports Center' ,

//     if(SalesOffice = 'OS','Outdoor Sports' ,

//     if(SalesOffice = 'BN','Bonitz' ,

//     if(SalesOffice = 'NT','Jerrys Northeast' ,

//     if(SalesOffice = 'SG','Simmons Guns' ,

//     if(SalesOffice = 'HS','Chapin Hunting' ,

//     if(SalesOffice = 'MA','Chapin Marine' , 'Invalid Office'))))))))))) as  SalesOfficeName ,

      [Order Type],

     [Original User Id],

     [Total Order Value],

     [Override Price Discount Percent],

//     [Packer ID],   No Data

//     [Picker ID],   No Data

     [Pick Slip Print Time],

     [Pick Slip Print User],

     [Print Acknowledgements],

     [Prior Hold Code],

     [Pro Number],

     [Parent Order Number],

     [Print Invoices Y/N],

     [Pick Slip Print Date],

     [PST Amount],

     [PST Tax Percent],

     [PST Yes/No/Compounded],

     [Payment Type Code],

     [Quote Review Date],

     Route,

     [Requested Ship Date],

     [Return to Stock],

     [Ship Confirm Date],

     [Total - Special Charges],

     [Ship Confirm Time],

     [Ship Confirm User],

     [Ship-to GeoCode],

     [Ship-To Number],

     [Shipped Order Weight],

     [Primary Salesman] as [Sales Representative Number],

     [Second Salesman],

     [Third Salesman],

     [Shipping Instructions],

     Stop,

     [Total Cubes Shipped],

     [Trade Discount Amount],

     [Trade Discount Percent],

     Territory,

     [A/R Terms Code],

     [Amount Tendered],

     [Sales Tax Amount],

     [Tax Body],

     [Sales Tax Percent],

//     [Use Orig Tax %],  No Data

     [Update A/R],

     [Update Demand],

     [Warehouse ID] as [Sales Warehouse ID]

FROM

C:\ProgramData\QlikTech\SourceDocuments\QVD\HSHED.qvd

(qvd)

WHERE([Company Number] <> 99);

2 Replies
MVP & Luminary
MVP & Luminary

Re: Calendar issue... How to get the dates of two tables to work together

chiso_chiso
Contributor

Re: Calendar issue... How to get the dates of two tables to work together

Looking for this link it appears broken, can you please provide a direct link?