topic Help With Missing Data In Straight Table in QlikView

Help With Missing Data In Straight Table

pnn44794 — Sat, 12 Oct 2019 23:46:12 GMT

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.

Re: Help With Missing Data In Straight Table

luismadriz — Tue, 05 Dec 2017 01:53:10 GMT

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

Re: Help With Missing Data In Straight Table

pnn44794 — Tue, 05 Dec 2017 14:44:31 GMT

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.

Re: Help With Missing Data In Straight Table

pnn44794 — Tue, 05 Dec 2017 19:12:13 GMT

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.

Re: Help With Missing Data In Straight Table

luismadriz — Tue, 05 Dec 2017 23:27:01 GMT

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

Re: Help With Missing Data In Straight Table

pnn44794 — Wed, 06 Dec 2017 01:16:28 GMT

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.

Re: Help With Missing Data In Straight Table

luismadriz — Wed, 06 Dec 2017 01:39:39 GMT

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]?

Re: Help With Missing Data In Straight Table

pnn44794 — Wed, 06 Dec 2017 01:47:18 GMT

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.

Re: Help With Missing Data In Straight Table

luismadriz — Wed, 06 Dec 2017 02:09:07 GMT

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

Re: Help With Missing Data In Straight Table

luismadriz — Wed, 06 Dec 2017 02:21:05 GMT

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

Re: Help With Missing Data In Straight Table

pnn44794 — Wed, 06 Dec 2017 02:22:31 GMT

I completely believe you Luis. There’s gotta be something weird on my side that’s causing this. At the end of the day though, I think I’m good at this point, Thank you very much again for taking the time to look into this and respond. I do appreciate it.

Re: Help With Missing Data In Straight Table

pnn44794 — Wed, 06 Dec 2017 02:24:55 GMT

I am not. I should note that there are other tables in the load, but this particular table is completely independent of the others. Thus, why I only provided its load script portion. There’s no explicit or implied joins with this table.