topic Substract 2nd and 3rd columns into 4th column in QlikView

Substract 2nd and 3rd columns into 4th column

ngreddy1982 — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Substract 2nd and 3rd columns into 4th column

sunny_talwar — Mon, 03 Apr 2017 15:45:39 GMT

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

Re: Substract 2nd and 3rd columns into 4th column

ngreddy1982 — Mon, 03 Apr 2017 15:49:37 GMT

Hi Sunny,

I have lot of columns next to them, so

Re: Substract 2nd and 3rd columns into 4th column

yoganantha321 — Mon, 03 Apr 2017 15:52:00 GMT

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

Re: Substract 2nd and 3rd columns into 4th column

sunny_talwar — Mon, 03 Apr 2017 15:53:31 GMT

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

Re: Substract 2nd and 3rd columns into 4th column

ngreddy1982 — Mon, 03 Apr 2017 15:55:59 GMT

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 .

Re: Substract 2nd and 3rd columns into 4th column

sunny_talwar — Mon, 03 Apr 2017 15:57:04 GMT

That's exactly what I mentioned here

LOAD [Order Date],

[Shipment Date],

[Registering Date],

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

FROM .....;

Re: Substract 2nd and 3rd columns into 4th column

Anil_Babu_Samineni — Mon, 03 Apr 2017 15:59:24 GMT

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

Re: Substract 2nd and 3rd columns into 4th column

ngreddy1982 — Mon, 03 Apr 2017 15:59:46 GMT

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

Re: Substract 2nd and 3rd columns into 4th column

sunny_talwar — Mon, 03 Apr 2017 16:00:46 GMT

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

Re: Substract 2nd and 3rd columns into 4th column

ngreddy1982 — Mon, 03 Apr 2017 16:04:24 GMT

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

load

HE.[Shipment Date],

[Registering Date],

[Registering Date] - [Shipment Date] as [Elapsed Days] FROM [TBW_BI].[dbo].[Feelunique Trading$Sales Invoice Header] ;

Re: Substract 2nd and 3rd columns into 4th column

sunny_talwar — Mon, 03 Apr 2017 16:08:03 GMT

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

Re: Substract 2nd and 3rd columns into 4th column

Anil_Babu_Samineni — Mon, 03 Apr 2017 16:15:22 GMT

May be this?

load

    HE.[Shipment Date],

    [Registering Date],

    [Registering Date] - HE.[Shipment Date] as [Elapsed Days] FROM [TBW_BI].[dbo].[Feelunique Trading$Sales Invoice Header] ;

Re: Substract 2nd and 3rd columns into 4th column

ngreddy1982 — Mon, 03 Apr 2017 16:16:59 GMT

Thank you Sunny.