Labeling Data?

[#1]

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?

[el_aprendiz111]

Hi

please send more data to help you.

[santiago_respane]

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,

[#4]

‌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.

[MarcoWedel · Accepted solution]

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

[santiago_respane]

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,

[#7]

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

[#8]

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.

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