Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable
‎2017-06-20 08:38 AM

Issues with data calculation against same field ID

Hi Can anyone help me,

I have a data file which presents the same data field ID over a period of 5 months; however each row of this field presents different data fields against the same data field ID.

For example is have the following:

10038796.0%Feb-17
10038781.7%Mar-17
10038784.4%Apr-17
10038784.8%May-17
10038769.8%Jun-17

Each 100387 is the same product code, and what I want to do is be able to input to QV each entry of 100387 with the percentages against each month, but I dont want QV to assume there are 5 x 100387's; i.e., I dont want QV to assume there are five (5) 100387 products.

Presently QV is assuming there are 5 x 100387, with 5 seperate %'s, rather than assuming that there is 1 x 100387 calculating %'s over 5 months.

I have a data file in excess of 25,000 entries; however there should only be around 5,000 products being processed by QV. Presetnly QV is takng every product code and then adding the same product code again as a count for a following months % data against the same product code.

Can anyone help?

850 Views
0 Likes
14 Replies
sunny_talwar
MVP
MVP
‎2017-06-20 11:37 AM

Are you looking to Count product codes? You can always do a count distinct to get this information

Count(DISTINCT [product code])

Otherwise, what exactly is the issue?

578 Views
0 Likes
Anonymous
Not applicable
‎2017-06-20 11:43 AM
Author

In response to sunny_talwar

Hi Sunny,

               sorry if my request was confusing.

I want to collect all data associated with a single product code; i.e., the 100387 has distinct data associated with it for each month; therefore I want QV to take account of this, rather than just assuming that if there are 5 months worth of data, then there must be 5 100387 products.

Hope this makes sense now?

Chris

578 Views
0 Likes
Anonymous
Not applicable
‎2017-06-20 11:49 AM
Author

In response to sunny_talwar

Hi Sunny,

               I need all data associated with product code 100387 to be accounted for within QV for each of the 5 months, rather than QV assuming that a product code of 100387 for Feb, 100387 for Mar, 100387 for Apr, 100387 for May, and 100387 for Jun are 5 100387 products.

QV seems to be assuming there are five products of 100387 in this example rather than one product 100387 with 5 months of data associated per month for 100387.

Hope this makes sense now?

Chris

578 Views
0 Likes
sunny_talwar
MVP
MVP
‎2017-06-20 12:12 PM

In response to Anonymous

It is not making that assumption. That's the way the data is stored. For each month, for the same product code it stores a percentage... what would you like your data to look like?

Product code,      month1, Month2, Month3

100387,     96%, 81.7%, 84.4%...

This is not a good way to load data, but if you want to do this, you can look here:

The Generic Load

578 Views
0 Likes
Anonymous
Not applicable
‎2017-06-20 12:38 PM
Author

In response to sunny_talwar

Hi Sunny,

                    I'd like to be able to have a % presented for each month against one 100387 product code once I select the month I desire.

At present the data is just beng accumulated for every entry of 100387, rather than taking all the 100387's and only presenting the % for that month once its been selected.

The overall issue is that I have 25,000+ lines of data around 5,000 (ish) product codes, over 5 months. QV is presently taking every product entry as a singular item, hence the reason why the quantities of Product codes is 25,000 rather than 5,000; e.g., 1 x 100387 per month, rather than 5 x 100387.

Kind Regards,

Chris

578 Views
0 Likes
sunny_talwar
MVP
MVP
‎2017-06-20 12:50 PM

In response to Anonymous

I understand what it is doing... I am trying to understand what you want it to do

578 Views
0 Likes
Anonymous
Not applicable
‎2017-06-21 06:55 AM
Author

In response to sunny_talwar

Hi Sunny,

               I can see I'm frustrating you a bit so I'll try and explain.

Please see test file below:

              

TypeMinor Product IDFull Product IDAsset IDRegionRoad / GeogRoad MPDevice StatusSTI StatusSite DescriptionKnown IssuesAvailabilityResponsibilityMonth
CAM10096000001,00960627875WMRCCM6/5834M6/183.4In ServiceLiveM6 Security cam 96000.1%NRTSFeb-17
CAM10096200001,00962627878WMRCCM6/5834M6/183.4In ServiceLiveM6 Security cam 96200.1%NRTSFeb-17
CAM10096400001,00964627879WMRCCM6/5835M6/183.5In ServiceLiveM6 Security cam 964088.0%NRTSFeb-17
CAM10096600001,00966627880WMRCCM6/5835M6/183.5In ServiceLiveM6 Security cam 96620.1%NRTSFeb-17
CAM10096800001,00968627898WMRCCM6/5835M6/183.5In ServiceLiveM6 Security cam 96810.1%NRTSFeb-17
CAM10097000001,00970627916WMRCCM6/5835M6/183.5In ServiceLiveM6 Security cam 97000.1%NRTSFeb-17
CAM10096000001,00960627875WMRCCM6/5834M6/183.4In ServiceLiveM6 Security cam 96000.1%NRTSMar-17
CAM10096200001,00962627878WMRCCM6/5834M6/183.4In ServiceLiveM6 Security cam 96200.1%NRTSMar-17
CAM10096400001,00964627879WMRCCM6/5835M6/183.5In ServiceLiveM6 Security cam 964081.9%NRTSMar-17
CAM10096600001,00966627880WMRCCM6/5835M6/183.5In ServiceLiveM6 Security cam 96620.1%NRTSMar-17
CAM10096800001,00968627898WMRCCM6/5835M6/183.5In ServiceLiveM6 Security cam 96810.1%NRTSMar-17
CAM10097000001,00970627916WMRCCM6/5835M6/183.5In ServiceLiveM6 Security cam 97000.1%NRTSMar-17
CAM10096000001,00960627875WMRCCM6/5834M6/183.4In ServiceLiveM6 Security cam 96003.4%NRTSApr-17
CAM10096200001,00962627878WMRCCM6/5834M6/183.4In ServiceLiveM6 Security cam 962012.2%NRTSApr-17
CAM10096400001,00964627879WMRCCM6/5835M6/183.5In ServiceLiveM6 Security cam 964055.2%NRTSApr-17
CAM10096600001,00966627880WMRCCM6/5835M6/183.5In ServiceLiveM6 Security cam 96620.0%NRTSApr-17
CAM10096800001,00968627898WMRCCM6/5835M6/183.5In ServiceLiveM6 Security cam 968131.0%NRTSApr-17
CAM10097000001,00970627916WMRCCM6/5835M6/183.5In ServiceLiveM6 Security cam 970013.1%NRTSApr-17
CAM10096000001,00960627875WMRCCM6/5834M6/183.4In ServiceLiveM6 Security cam 960085.0%NRTSMay-17
CAM10096200001,00962627878WMRCCM6/5834M6/183.4In ServiceLiveM6 Security cam 962095.4%NRTSMay-17
CAM10096400001,00964627879WMRCCM6/5835M6/183.5In ServiceLiveM6 Security cam 964095.0%NRTSMay-17
CAM10096600001,00966627880WMRCCM6/5835M6/183.5In ServiceLiveM6 Security cam 96620.0%NRTSMay-17
CAM10096800001,00968627898WMRCCM6/5835M6/183.5In ServiceLiveM6 Security cam 968114.7%NRTSMay-17
CAM10097000001,00970627916WMRCCM6/5835M6/183.5In ServiceLiveM6 Security cam 970095.4%NRTSMay-17
CAM10096000001,00960627875WMRCCM6/5834M6/183.4In ServiceLiveM6 Security cam 960095.1%NRTSJun-17
CAM10096200001,00962627878WMRCCM6/5834M6/183.4In ServiceLiveM6 Security cam 962095.3%NRTSJun-17
CAM10096400001,00964627879WMRCCM6/5835M6/183.5In ServiceLiveM6 Security cam 964095.3%NRTSJun-17
CAM10096600001,00966627880WMRCCM6/5835M6/183.5In ServiceLiveM6 Security cam 96620.0%NRTSJun-17
CAM10096800001,00968627898WMRCCM6/5835M6/183.5In ServiceLiveM6 Security cam 96810.0%NRTSJun-17
CAM10097000001,00970627916WMRCCM6/5835M6/183.5In ServiceLiveM6 Security cam 970095.5%NRTSJun-17

The above is a small snap shot of roughly 25,000 rows of data from around 5,000 product IDs. What I'd like to do firstly is be able to get QV to calculate the quantity of Full Product IDs; for example you can see that the same Product IDs are repeated for each seperate Month for Feb, Mar, Apr, May, and June. However what QV is presently doing is assuming that is 0001,00960 is present in Feb, Mar, Apr, May, and Jun, that when QV performs a count, there is 5 x 0001,00960 Product IDs, rather than the same Product ID being represented with different monthly data. For the example file above there is actually only 6 Product IDs in Feb, then 12 Product IDs in Mar; however the same 6 Product IDs from Feb are part of the 12.What QV is presently doing when it performs a count of the Product IDs from the above example file, is just adding them all up without accounting for the repeate Product IDs. The QV count in the example file above shows 30 Product IDs; however in reality there are only ever a maximum of 12 Product IDs, from the 30 as the rest are just repeated. If you can imagine this same issue repeating itself over 5,000 Product IDs, you can see how QV at the moment is retruning something like 25,000 Product IDs.

I'm hoping this now makes more sense. If I can just achieve an actual count for the Product IDs without including any repeat Product IDs, rather than a complete count of Product IDs including repeat Product IDs where the same Product IDs are repeated per Month, then this would be a great help.

Kind Regards,

Chris

578 Views
0 Likes
sunny_talwar
MVP
MVP
‎2017-06-21 07:25 AM

In response to Anonymous

I am not frustrated my friend... like I mentioned below, QlikView can count your distinct ProductID, you just need to tell it to do so...

Capture.PNG

The first text box is doing a distinct count of Full Product ID using this expression

Count(DISTINCT [Full Product ID])

I know you mentioned 12, but I only see 6 distinct Full Product IDs in your sample... so I am guessing you might have put that down by mistake

Capture.PNG

Now, feel free to tell me that this is not what you wanted and may be we can talk about what you really want to see in the dashboard. But from what i read, this is what you are looking to get.

Best,

Sunny

264727.qvw
578 Views
0 Likes
Anonymous
Not applicable
‎2017-06-21 07:36 AM
Author

In response to sunny_talwar

Hi Sunny,

               thanks for this, before I try it, is there a way of loading the file, rather than inputing the whole data from the file in the script. As you can imagine doing this for 25,000 entries would be a bit extreme. Can I point the LOAD * to the file, rather than the below from the test example file:

Table:

LOAD *,

     Date(MonthStart(Date#(Month, 'MMM-YY')), 'MMM-YY') as MonthYear;

LOAD * INLINE [

    Type, Minor Product ID, Full Product ID, Asset ID, Region, Road / Geog, Road MP, Device Status, STI Status, Site Description, Known Issues, Availability, Responsibility, Month

    CAM, 100960, "00001,00960", 627875, WMRCC, M6/5834, M6/183.4, In Service, Live, M6 Security cam 960, 0, 0.1%, NRTS, Feb-17

    CAM, 100962, "00001,00962", 627878, WMRCC, M6/5834, M6/183.4, In Service, Live, M6 Security cam 962, 0, 0.1%, NRTS, Feb-17

    CAM, 100964, "00001,00964", 627879, WMRCC, M6/5835, M6/183.5, In Service, Live, M6 Security cam 964, 0, 88.0%, NRTS, Feb-17

    CAM, 100966, "00001,00966", 627880, WMRCC, M6/5835, M6/183.5, In Service, Live, M6 Security cam 966, 2, 0.1%, NRTS, Feb-17

    CAM, 100968, "00001,00968", 627898, WMRCC, M6/5835, M6/183.5, In Service, Live, M6 Security cam 968, 1, 0.1%, NRTS, Feb-17

    CAM, 100970, "00001,00970", 627916, WMRCC, M6/5835, M6/183.5, In Service, Live, M6 Security cam 970, 0, 0.1%, NRTS, Feb-17

    CAM, 100960, "00001,00960", 627875, WMRCC, M6/5834, M6/183.4, In Service, Live, M6 Security cam 960, 0, 0.1%, NRTS, Mar-17

    CAM, 100962, "00001,00962", 627878, WMRCC, M6/5834, M6/183.4, In Service, Live, M6 Security cam 962, 0, 0.1%, NRTS, Mar-17

    CAM, 100964, "00001,00964", 627879, WMRCC, M6/5835, M6/183.5, In Service, Live, M6 Security cam 964, 0, 81.9%, NRTS, Mar-17

    CAM, 100966, "00001,00966", 627880, WMRCC, M6/5835, M6/183.5, In Service, Live, M6 Security cam 966, 2, 0.1%, NRTS, Mar-17

    CAM, 100968, "00001,00968", 627898, WMRCC, M6/5835, M6/183.5, In Service, Live, M6 Security cam 968, 1, 0.1%, NRTS, Mar-17

    CAM, 100970, "00001,00970", 627916, WMRCC, M6/5835, M6/183.5, In Service, Live, M6 Security cam 970, 0, 0.1%, NRTS, Mar-17

    CAM, 100960, "00001,00960", 627875, WMRCC, M6/5834, M6/183.4, In Service, Live, M6 Security cam 960, 0, 3.4%, NRTS, Apr-17

    CAM, 100962, "00001,00962", 627878, WMRCC, M6/5834, M6/183.4, In Service, Live, M6 Security cam 962, 0, 12.2%, NRTS, Apr-17

    CAM, 100964, "00001,00964", 627879, WMRCC, M6/5835, M6/183.5, In Service, Live, M6 Security cam 964, 0, 55.2%, NRTS, Apr-17

    CAM, 100966, "00001,00966", 627880, WMRCC, M6/5835, M6/183.5, In Service, Live, M6 Security cam 966, 2, 0.0%, NRTS, Apr-17

    CAM, 100968, "00001,00968", 627898, WMRCC, M6/5835, M6/183.5, In Service, Live, M6 Security cam 968, 1, 31.0%, NRTS, Apr-17

    CAM, 100970, "00001,00970", 627916, WMRCC, M6/5835, M6/183.5, In Service, Live, M6 Security cam 970, 0, 13.1%, NRTS, Apr-17

    CAM, 100960, "00001,00960", 627875, WMRCC, M6/5834, M6/183.4, In Service, Live, M6 Security cam 960, 0, 85.0%, NRTS, May-17

    CAM, 100962, "00001,00962", 627878, WMRCC, M6/5834, M6/183.4, In Service, Live, M6 Security cam 962, 0, 95.4%, NRTS, May-17

    CAM, 100964, "00001,00964", 627879, WMRCC, M6/5835, M6/183.5, In Service, Live, M6 Security cam 964, 0, 95.0%, NRTS, May-17

    CAM, 100966, "00001,00966", 627880, WMRCC, M6/5835, M6/183.5, In Service, Live, M6 Security cam 966, 2, 0.0%, NRTS, May-17

    CAM, 100968, "00001,00968", 627898, WMRCC, M6/5835, M6/183.5, In Service, Live, M6 Security cam 968, 1, 14.7%, NRTS, May-17

    CAM, 100970, "00001,00970", 627916, WMRCC, M6/5835, M6/183.5, In Service, Live, M6 Security cam 970, 0, 95.4%, NRTS, May-17

    CAM, 100960, "00001,00960", 627875, WMRCC, M6/5834, M6/183.4, In Service, Live, M6 Security cam 960, 0, 95.1%, NRTS, Jun-17

    CAM, 100962, "00001,00962", 627878, WMRCC, M6/5834, M6/183.4, In Service, Live, M6 Security cam 962, 0, 95.3%, NRTS, Jun-17

    CAM, 100964, "00001,00964", 627879, WMRCC, M6/5835, M6/183.5, In Service, Live, M6 Security cam 964, 0, 95.3%, NRTS, Jun-17

    CAM, 100966, "00001,00966", 627880, WMRCC, M6/5835, M6/183.5, In Service, Live, M6 Security cam 966, 2, 0.0%, NRTS, Jun-17

    CAM, 100968, "00001,00968", 627898, WMRCC, M6/5835, M6/183.5, In Service, Live, M6 Security cam 968, 1, 0.0%, NRTS, Jun-17

    CAM, 100970, "00001,00970", 627916, WMRCC, M6/5835, M6/183.5, In Service, Live, M6 Security cam 970, 0, 95.5%, NRTS, Jun-17

Thanks, Chris

578 Views
0 Likes