topic Re: Labeling Data? in QlikView

Labeling Data?

Wed, 25 Nov 2020 16:16:04 GMT

Good evening everyone,

I am new to Qlikview and am still tinkering around with it. I am importing a bunch of data from Excel, below is the sample script.

LOAD date(Opened,'DD/MM/YYYY') as [Date Opened],

date(Closed,'DD/MM/YYYY') as [Date Closed],

month(date(Opened,'DD/MM/YYYY')) as [Month],

year(date(Opened,'DD/MM/YYYY')) as [Year],

[Configuration item],

Category,

Stage,

Region

Under configuration item there are a bunch of computer names(1G212317 for example). Could anyone please point me on how to filter it so that all the computer names gets consolidated and renamed as 'Computer' under configuration item?

Re: Labeling Data?

el_aprendiz111 — Mon, 19 Sep 2016 16:04:41 GMT

please send more data to help you.

Re: Labeling Data?

santiago_respane — Mon, 19 Sep 2016 16:17:36 GMT

Hi,

if i didnt get it wrong you want to set Computer as value for the field Configuration Item?

If that's the case you can use the AS statement in your load as follows:

LOAD date(Opened,'DD/MM/YYYY') as [Date Opened],

date(Closed,'DD/MM/YYYY') as [Date Closed],

month(date(Opened,'DD/MM/YYYY')) as [Month],

year(date(Opened,'DD/MM/YYYY')) as [Year],

'Computer' as [Configuration item],

Category,

Stage,

Region

Please let me know if this helps.

Kind regards,

Re: Labeling Data?

Mon, 19 Sep 2016 22:33:41 GMT

‌Sorry for the confusion, attaching a picture here to help clear things up. As you can see under Configuration Item there is a bunch of data starting with 1 and ending with another number. These are all computers, would it be possible to make it so that whenever Qlikview detects such data it automatically creates a group called Computer in Configuration Item and lists all such data under it? That way it helps to make things look neater as there would only be one group called Computer there instead of a bunch of data that don't really make sense.

Re: Labeling Data?

MarcoWedel — Mon, 19 Sep 2016 23:05:48 GMT

Hi,

maybe something like:

If([Configuration item] like '1??????' and IsNum(Right([Configuration item],1)),'Computer',[Configuration item]) as [Configuration item]

hope this helps

regards

Marco

Re: Labeling Data?

santiago_respane — Tue, 20 Sep 2016 13:17:41 GMT

Hi,

how about something like this:

LOAD date(Opened,'DD/MM/YYYY') as [Date Opened],

date(Closed,'DD/MM/YYYY') as [Date Closed],

month(date(Opened,'DD/MM/YYYY')) as [Month],

year(date(Opened,'DD/MM/YYYY')) as [Year],

IF(left([Configuration item],1)=1 and alt(right([Configuration item],1)),'Computer','Not Computer') as [Configuration Item Category],

[Configuration item],

Category,

Stage,

Region

This will add a new field called [Configuration Item Category] that will let you group/label your data so that you dont loose your values in the Configuration Item field.

Please let me know if this helps.

Kind regards,

Re: Labeling Data?

Wed, 21 Sep 2016 06:11:33 GMT

Hey Marco! Thanks! This made those that fit the criteria get slotted in right under Computer.

I made another if using if isText in it and it works too. Though is this the recommended way to filter data? Or is there a better way that I am not aware of?

When it comes to computer with names like C14T72S or DFD723G I am not too sure how to apply the if conditions without it affecting other items too. Also when I try to use AND to create another group in Configuration Category it just goes blank.

This is what my current script looks like.

If([Configuration item] LIKE 'AU1*' 
     or [Configuration item] LIKE 'DE1*'
     or [Configuration item] LIKE 'P05*'
     or [Configuration item] LIKE 'P15*'
     or [Configuration item] LIKE 'P21*'
     or [Configuration item] LIKE 'P25*'
     or [Configuration item] LIKE 'S12*'
     or [Configuration item] LIKE 'S13*'
     or [Configuration item] LIKE 'TST*','System',[Configuration item]) AND
     If(IsText(Left([Configuration item],1)) and IsNum(Right([Configuration item],1)) 
     or IsNum(Left([Configuration item],1)),'Computer',[Configuration item]) as [Configuration category],

Re: Labeling Data?

Wed, 21 Sep 2016 06:16:11 GMT

Hey Santiago,

That's sort of what I did based on Marco's suggestion! This is what it looks like now. Configuration item is still kept inside.

This is what my current script looks like.

If([Configuration item] LIKE 'AU1*' 
     or [Configuration item] LIKE 'DE1*'
     or [Configuration item] LIKE 'P05*'
     or [Configuration item] LIKE 'P15*'
     or [Configuration item] LIKE 'P21*'
     or [Configuration item] LIKE 'P25*'
     or [Configuration item] LIKE 'S12*'
     or [Configuration item] LIKE 'S13*'
     or [Configuration item] LIKE 'TST*','System',[Configuration item]) AND
     If(IsText(Left([Configuration item],1)) and IsNum(Right([Configuration item],1)) 
     or IsNum(Left([Configuration item],1)),'Computer',[Configuration item]) as [Configuration category],