Help With Missing Data In Straight Table

[pnn44794]

Hello:

I have a straight table where I'm expecting 6,413 rows of data to be loaded and I'm only getting 5,886 rows loaded.  When loading the data.

[luismadriz]

Hi,

You only need to add the date function to your script for allow in the data you want. Please try and then we'll take a look at your calculated dimension and measure.

I hope this helps,

Cheers,

Luis

[pnn44794]

Hello Luis:

Thank you for your reply and my apologies for the delay in mine.  I made this change to the Where statement to match up with yours:  Where LOB = 'CCB' and [Order Create Date] >= date('01/01/17');

I then reran the load script.  The script fetched 1,735 lines and still shows 2016 dates.

My straight table displays 1,568 rows.  If I remove Order Create Date and Approval Complete date from the straight table, it displays 1,567 rows.  Very weird.............at least for me.

[pnn44794 · Accepted solution]

Hello Luis:

Update.  I still have not been able to get the [Order Create Date] >= date('01/01/17') to work as 2016 dates still pull in when I run the load script.  I'm not going to worry too much about it since the orders were actually completed in 2017 anyway and I think people will be ok with that.

The good news is that I figured out the row problem.  This is a bit embarrassing, but it helps when you include a column where every row is populated and not null or blank.  I was trying to use the myCompute Order ID column and it's not populated on every row.    I got all the rows now.  So it's just the date filter in the load scrip that's not working.

[luismadriz]

Hi Perry,

Can you please confirm your date format to be this before running it?

SET DateFormat='D/M/YYYY';

SET TimestampFormat='D/M/YYYY h:mm:ss[.fff] TT';

This is the script I'm running and getting 6,143 records with nothing from 2016:

[E2E-DASHBOARD-DATA]:

LOAD

     [WRM Number] as [WRM-Number],

//     [WRM Title],

     [Application #],

//     [Application Name],

//     SubField(Site, ', ', 2) as Site,

//     Requestor as REQUESTOR,

//     [Approver Name],

     [Service Ordered],

     [Template or Non Template],

     [Service Tier ( myCompute )],

     [SKU Name (myCompute)],

     [Service Tier Cleaned],

     "[Environment-VSI (myCompute)",

     [Environment (TAMS Type)],

//     [KeON Domain],

     [Class ( TAMS )],

     [Business Load (TAMS)],

     [Status (TAMS)],

     [Sub Status (TAMS)],

     [Open myCompute Stage or New Install Post Build Prime WO Request Group],

     [Queue Open Group],

     [Open New Install Post Build Prime WO Request ID],

     [E2E Status],

     [myCompute & EURC Order Status],

     [myCompute Order Item Status],

     BRN as [BRN-ID],

     Mid(BRN, 3, 7) as [LOB Template Order],

     [Project Number],

     Milestone,

     [Line Item],

     [Parent Line Item ID],

     Num(SubField(Trim(Replace(Replace(SubField(Replace(Upper([SKU Name (myCompute)]), 'CPU', 'CORE PROCESSING UNIT'), 'CORE', 1), '-', ''), ',', ' ')), ' ', -1)) as CPU,

     Num(If(WildMatch(Memory, '*-*'), Mid(Memory, 4), Memory), '#0') as MEM,

     EBR,

     [myCompute Order ID],

     [EURC Order ID],

     [Constrained or Exit site approval        Prime ID],

     [Build OS         Prime ID],

     [New Install Post Build        Prime ID],

//     [Host Name],

     LOB as [Line-Of-Business],

     [BRN Build Type],

     [Product Build End Type],

     [End 2 End Asset Order Status],

     [Service New],

     [** E2E-Ordering System],

     Date([Order Create Date], 'MM/DD/YYYY') as [Order Create Date],

     Date([Approval Complete Date], 'MM/DD/YYYY') as [Approval Complete Date],

     [Provisioning Quality Check Completed Date],

     [Productionised Create Date],

     [Promise Date],

     [Trend Date],

     [E2E SLO],

     [Promised Lead time],

     [Business Days End 2 END ( with Approvals )],

     [Business Days End 2 END ( without Approvals )],

     [Order Complete Date],

     [Asset Completed Month],

     [Completed Start Week (Mon)],

     [OS Provisioning Start Date],

     [Provisioning OS - PaaS Business Days (EMR)],

     [Asset Type (myCompute)]

//     [myCompute Notes (hist)],

//     [myCompute Notes (Latest Update)],

//     [Prime All On Hold Comments]

FROM [lib://AttachedFiles/Missing-Data-From-This-Spreadsheet.xlsx]

(ooxml, embedded labels, table is [End2End (Summary)])

Where LOB = 'CCB' and [Order Create Date] >= Date('01/01/17');

I hope this helps,

Cheers,

Luis

[pnn44794]

Hello Luis:

You have been very helpful.  I've believe I've solved the problem though.  I simply needed to have the right fields added (my fault for not originally) and then the date thing, really doesn't matter.

For the record, I tried what you just suggested but I still get 2016 dates.  Again though, I think that's ok because of when they were completed by (in 2017).

Thank you for taking the time to respond.  I do appreciate it.

[luismadriz]

Hi,

So you're saying that if you try the load script I sent last and confirm the DateFormat setting I sent, you still get 2016 dates in the field [Order Create Date]?

[pnn44794]

Yes and worse, I only wind up with 1,735 rows fetched when I run the load script.  Very weird and I'm guessing you're thinking the same.

[luismadriz]

That's very strange!

No kidding it's strange. I just downloaded again your data and confirmed it in Excel and in Qlik... with the same setting and load script that I sent

I hate not being able to troubleshoot something like this...

Any way, good luck!

Cheers,

Luis

[luismadriz]

You're not doing a join with that table or anything else right?