need help

[#1]

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

[Anonymous]

See attachment!

[#3]

Hi Swathi,

Please find attached.

[#4]

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

[alexandros17]

My Example

[MayilVahanan]

Hi

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')

[#7 · Accepted solution]

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

[#8]

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

[#9]

Thanks all for your quick responses

[Anonymous]

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.