topic Re: need help in QlikView

need help

Tue, 24 Jun 2014 07:19:32 GMT

Hi I have a requirement like below

Country	STARTTIME	ENDTIME	SALES/ORDERS
SALES_US	06/24/14	06/24/14	100
ORDERS_US	06/24/14	06/24/14	200
SALES_UK	06/24/14	06/23/14	150
ORDERS_UK	06/23/14	06/24/14	250
SALES_IN	06/24/14	06/23/14	80
ORDERS_IN	06/24/14	06/24/14	120

output looks like below

Country	Sales	Orders	SalesStatus(If STARTTIME=ENDTIME I need to display Green Traffic light else red Red Traffic light)
US	100	200	(green color Traffic light)
UK	150	250	(red color)
IN	80	120	(redcolor)

same logic as for Order Status also.

Can any one help me out thanks in advance

Re: need help

Anonymous — Tue, 24 Jun 2014 07:29:12 GMT

See attachment!

Re: need help

Tue, 24 Jun 2014 07:36:20 GMT

Hi Swathi,

Please find attached.

Re: need help

Tue, 24 Jun 2014 07:37:02 GMT

Hi Satyadev

thanks for quick response but I am using personal edition in my desktop unable to open,can you please share the logic here

Thanks

Swathi

Re: need help

alexandros17 — Tue, 24 Jun 2014 07:37:11 GMT

My Example

Re: need help

MayilVahanan — Tue, 24 Jun 2014 07:40:00 GMT

PFA

Edit:

Load If(WildMatch(Country, '*Sales*'), [SALES/ORDERS],0) AS Sales, If(WildMatch(Country, '*Orders*'), [SALES/ORDERS],0) AS Orders, SubField(Country,'_',2) As CountryName, *;

LOAD * INLINE [

Country, STARTTIME, ENDTIME, SALES/ORDERS

SALES_US, 06/24/14, 06/24/14, 100

ORDERS_US, 06/24/14, 06/24/14, 200

SALES_UK, 06/24/14, 06/23/14, 150

ORDERS_UK, 06/23/14, 06/24/14, 250

SALES_IN, 06/24/14, 06/23/14, 80

ORDERS_IN, 06/24/14, 06/24/14, 120

];

In Chart

CountryName As Dimension

Expression:

=Sum(Sales)

Sum(Orders)

=If(ENDTIME = STARTTIME, 'qmem://<bundled>/BuiltIn/led_g.png','qmem://<bundled>/BuiltIn/led_r.png')

Re: need help

Tue, 24 Jun 2014 07:40:41 GMT

In the script you do the following:

TEMP:

LOAD * INLINE [

Country, STARTTIME, ENDTIME, SALES

SALES_US, 06/24/14, 06/24/14, 100

SALES_UK, 06/24/14, 06/23/14, 150

SALES_IN, 06/24/14, 06/23/14, 80

ORDERS_US, 06/24/14, 06/24/14, 200

ORDERS_UK, 06/23/14, 06/24/14, 250

ORDERS_IN, 06/24/14, 06/24/14, 120];

DATA:

NOCONCATENATE

LOAD *,

right(Country, 2) as [Country Short],

left(Country, 1) as [Order Type]

RESIDENT TEMP;

DROP TABLE TEMP;

And in the chart you do the following:

=if(STARTTIME = ENDTIME, 'qmem://<bundled>/BuiltIn/led_g.png', 'qmem://<bundled>/BuiltIn/led_r.png')

Regards,

Marius

Re: need help

Tue, 24 Jun 2014 07:43:15 GMT

O yeah. And you have to change the presentation to Image in the Expression tab for that specific expression in the Straight Table.

Re: need help

Tue, 24 Jun 2014 07:56:29 GMT

Thanks all for your quick responses

Re: need help

Anonymous — Tue, 24 Jun 2014 08:01:48 GMT

Here is the backend script,

TBL:

LOAD SubField(Country, '_',2) as Country,

STARTTIME,

ENDTIME,

If(SubField(Country, '_',1)='SALES', [SALES/ORDERS]) as SALES,

If(SubField(Country, '_',1)='ORDERS', [SALES/ORDERS]) as ORDERS

INLINE [

Country, STARTTIME, ENDTIME, SALES/ORDERS

SALES_US, 06/24/14, 06/24/14, 100

ORDERS_US, 06/24/14, 06/24/14, 200

SALES_UK, 06/24/14, 06/23/14, 150

ORDERS_UK, 06/23/14, 06/24/14, 250

SALES_IN, 06/24/14, 06/23/14, 80

ORDERS_IN, 06/24/14, 06/24/14, 120

];

In front end, create straight table chart,

Dimension:

County

Expression:

SALES column: Sum (SALES)

ORDERS column: Sum (ORDERS)

Status: If(STARTTIME=ENDTIME, 'qmem://<bundled>/BuiltIn/led_g.png','qmem://<bundled>/BuiltIn/led_r.png')

Remember to select "Keep Aspect" from Image formatting option available on expression tab for Status.

Re: need help

MarcoWedel — Tue, 24 Jun 2014 10:46:57 GMT

Hi,

even if your thread is already answered, just another solution:

LOAD SubField(Country, '_', 2) as Country,
     SubField(Country, '_', 1) as Type,
     STARTTIME = ENDTIME as Status,
     [SALES/ORDERS]
FROM [http://community.qlik.com/thread/122988] (html, codepage is 1252, embedded labels, table is @1);

pivot table:

created like this:

'qmem://<bundled>/BuiltIn/led_'&If(Status,'g','r')&'.png'

hope this helps

regards

Marco