Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
I need little help with one of my scripts. Below, I have script which can "flag" first invoice of customer based on date. But I also need to "flag" all sales of this customer in month of his first invoice. For example his first invoice was in Jan 2019 but he also had other invoices in Jan 2019. I need to flag all of his invoices in that month. Column FirstMonths_Invoices_flag in picture.
Invoice_items: LOAD Invoice_item_invoice_id, Invoice_item_generated_datetime, Invoice_item_user_id, Invoice_item_price FROM [lib://InvoiceItem_D.QVD] (qvd); Invoice_FirstOccurrence_TMP: LOAD Invoice_item_user_id, min(Invoice_item_generated_datetime) as MinInvoice Resident Invoice_items Group By Invoice_item_user_id; left join(Invoice_items) LOAD Invoice_item_user_id, MinInvoice as Invoice_item_generated_datetime, 'FirstInvoice' as Invoice_user_first_invoice_attribute Resident Invoice_FirstOccurrence_TMP;Output should be:
Try the following:
Invoice_items_RAW: LOAD Invoice_item_invoice_id, Invoice_item_generated_datetime, Year(Invoice_item_generated_datetime)*100 + Month(Invoice_item_generated_datetime) as Invoice_item_YearMonth, Invoice_item_user_id, Invoice_item_price FROM [lib://InvoiceItem_D.QVD] (qvd); Invoice_Items: NoConcatenate Load Invoice_item_invoice_id, Invoice_item_generated_datetime, Invoice_item_YearMonth, Invoice_item_user_id, Invoice_item_price, if (Invoice_item_user_id <> Previous(Invoice_item_user_id), True(), False() ) as First_Invoice_Flag Resident Invoice_items_RAW Order by Invoice_item_user_id, Invoice_item_generated_datetime, Invoice_item_invoice_id; Left Join(Invoice_Items) Load Distinct Invoice_item_user_id, Invoice_item_YearMonth, if (Invoice_item_user_id <> Previous(Invoice_item_user_id), True(), False() ) as FirstMonths_Invoices_flag Resident Invoice_items_RAW Order by Invoice_item_user_id, Invoice_item_YearMonth; Drop Table Invoice_items_RAW;
Try the following:
Invoice_items_RAW: LOAD Invoice_item_invoice_id, Invoice_item_generated_datetime, Year(Invoice_item_generated_datetime)*100 + Month(Invoice_item_generated_datetime) as Invoice_item_YearMonth, Invoice_item_user_id, Invoice_item_price FROM [lib://InvoiceItem_D.QVD] (qvd); Invoice_Items: NoConcatenate Load Invoice_item_invoice_id, Invoice_item_generated_datetime, Invoice_item_YearMonth, Invoice_item_user_id, Invoice_item_price, if (Invoice_item_user_id <> Previous(Invoice_item_user_id), True(), False() ) as First_Invoice_Flag Resident Invoice_items_RAW Order by Invoice_item_user_id, Invoice_item_generated_datetime, Invoice_item_invoice_id; Left Join(Invoice_Items) Load Distinct Invoice_item_user_id, Invoice_item_YearMonth, if (Invoice_item_user_id <> Previous(Invoice_item_user_id), True(), False() ) as FirstMonths_Invoices_flag Resident Invoice_items_RAW Order by Invoice_item_user_id, Invoice_item_YearMonth; Drop Table Invoice_items_RAW;