Solved: Re: Substract 2nd and 3rd columns into 4th column - Qlik Community

ngreddy1982 · ‎2017-04-03

I have created a columns using "Concatenate Load * Inline [Elapsed Days];"

Now I want to subtract how many days between shipment date to registering date.

Please help.

sunny_talwar · ‎2017-04-03

Try this:

LOAD *,

[Registering Date] - [Shipment Date] as [Elapsed Days];

SELECT

HE.[Original Order No_]

,[Internet Order No_]

,HA.[Order Date]

,[Item No_]

,HE.[Bill-to Name]

,RE.[Description]

,[Brand Code]

,RE.[Reason Code]

,HE.[Shipment Date]

,[Quantity]

,[Country]

,HE.[Shipping Agent Code]

,[Comments]

,[Entry No_]

,[Registering Date]

,[Zone Code]

,[Bin Code]

,RE.[Location Code]

,[Replaced_Refunded]

,[Cust__Post Return]

,RE.[User ID]

,[Unit of Measure Code]

,[Gross Weight]

FROM [TBW_BI].[dbo].[Feelunique Trading$Warehouse Returns] as RE

INNER JOIN [TBW_BI].[dbo].[Feelunique Trading$Item] AS IT

ON IT.[No_]=RE.[Item No_]

INNER JOIN [TBW_BI].[dbo].[Feelunique Trading$Sales Invoice Header] AS HE

ON HE.[Order GuID]=RE.[Internet Order No_]

INNER JOIN [TBW_BI].[dbo].[Feelunique Trading$Internet Order Header] AS HA

ON HA.[Order Guid]=RE. [Internet Order No_]

WHERE

HA.[Order Date] >= '2017-01-01T00:00:00.000' AND

HA.[Order Date] <= '2017-01-31T00:00:00.000' ;

View solution in original post

sunny_talwar · ‎2017-04-03

Why concatenate load?

I think all your need to do is this

LOAD [Order Date],

[Shipment Date],

[Registering Date],

[Registering Date] - [Shipment Date] as [Elapsed Days]

FROM .....;

ngreddy1982 · ‎2017-04-03

Hi Sunny,

I have lot of columns next to them, so

yoganantha321 · ‎2017-04-03

Giri,

Date#([shipmentdate], 'MM/DD/YYYY') > 0,Today() - Date#([registeringdate], 'MM/DD/YYYY') as SLA_Aging

I think the above statement will be useful for you

sunny_talwar · ‎2017-04-03

so...? keeping typing... what is the issue?

ngreddy1982 · ‎2017-04-03

I have report showing with columns order no, order date, ship date , return date , item ,etc ,

now i have to create a column with difference between ship date and return date which is called elapsed days .

sunny_talwar · ‎2017-04-03

That's exactly what I mentioned here

LOAD [Order Date],

[Shipment Date],

[Registering Date],

[Registering Date] - [Shipment Date] as [Elapsed Days]

FROM .....;

Anil_Babu_Samineni · ‎2017-04-03

Or may be this? His intention is this? After statement we can load remaining columns also

LOAD [Order Date],

[Shipment Date],

[Registering Date],

[Registering Date] - [Shipment Date] as [Elapsed Days],

[order no], [ship date] , [return date] , [item]

FROM .....;

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

ngreddy1982 · ‎2017-04-03

Tried that it is not creating any column or any dimensions to add to chart .

sunny_talwar · ‎2017-04-03

Would you be able to share the script you have tried?