Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
pnn44794
Partner - Specialist
Partner - Specialist

Help With Missing Data In Straight Table

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.

1 Solution

Accepted Solutions
pnn44794
Partner - Specialist
Partner - Specialist
Author

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.

View solution in original post

11 Replies
luismadriz
Specialist
Specialist

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.

Untitled.png

I hope this helps,

Cheers,

Luis

pnn44794
Partner - Specialist
Partner - Specialist
Author

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
Partner - Specialist
Partner - Specialist
Author

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
Specialist
Specialist

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
Partner - Specialist
Partner - Specialist
Author

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
Specialist
Specialist

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
Partner - Specialist
Partner - Specialist
Author

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
Specialist
Specialist

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

Untitled.png

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

Any way, good luck!

Cheers,

Luis

luismadriz
Specialist
Specialist

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