Solved: Re: Re: Need help on the back end Coding - Qlik Community

Report Inappropriate Content · ‎2014-10-28

WHILE LOADING I HAVE

DATE GF FM S U

01/01/2014 0 1.6 2.3 0.8

02/01/2014 1.1 1.1 0.6 1.7

03/01/2014 2.3 0.9 1.3 0.5

04/01/2014 1.2 1.0 2.1 0.7

05/01/2014 1.5 1.6 3.1 2.6

06/01/2014 1.9 0.9 1.9 0.7

On the backend coding only i want to create

DATE GF gf FM fm S s U

01/01/2014 0 0 1.6 1.6 2.3 0.8

02/01/2014 1.1 1.1 1.1 2.7 0.6 1.7

03/01/2014 2.3 3.4 0.9 3.6 1.3 0.5

04/01/2014 1.2 4.6 1.0 4.6 2.1 0.7

05/01/2014 1.5 6.1 1.6 6.2 3.1 2.6

06/01/2014 1.9 8.0 0.9 7.1 1.9 0.7

I want to create a coloumn that will sum it up all the above GF data and FM data and S data and U data

Kushal_Chawda · ‎2014-10-28

Please see the attached

View solution in original post

Sokkorn · ‎2014-10-28

Hi Navneet,

Here is sample script

[Data]:
LOAD * Inline [
DATE,                 GF  
01/01/2014,          0 
02/01/2014,          1.1
03/01/2014,          2.3
04/01/2014,          1.2
05/01/2014,          1.5
06/01/2014,          1.9];

[Final]:
LOAD
  *,
  RangeSum([GF], Peek('GF_Accumulated')) AS [GF_Accumulated]
Resident [Data] Order By [DATE];

DROP Table [Data];

Regards,

Sokkorn

Report Inappropriate Content · ‎2014-10-28

Your answer is correct .. thanks... but i need one more help

suppose

Date Phase GF

01/01/2014 CHINA 0

01/01/2014 INDIA 0.7

01/01/2014 JAPAN 0.5

02/01/2014 CHINA 0.2

02/01/2014 INDIA 0.5

02/01/2014 JAPAN 0.3

03/01/2014 CHINA 0.8

03/01/2014 INDIA 0.4

03/01/2014 JAPAN 1.0

THE REQUIREMENT IS TO GET GF ACCUMULATED PHASE WISE

Date Phase GF GFACCUMILATED

01/01/2014 CHINA 0 0

02/01/2014 CHINA 0.2 0.2

03/01/2014 CHINA 0.8 0.8

01/01/2014 INDIA 0.7 0.7

02/01/2014 INDIA 0.5 1.2

03/01/2014 INDIA 0.4 1.6

01/01/2014 JAPAN 0.5 0.5

02/01/2014 JAPAN 0.3 0.8

03/01/2014 JAPAN 1.0 1.8

Please help

Sokkorn · ‎2014-10-28

Hi Naneet,

Here is my idea

[Data]:
LOAD * Inline [
DATE,               Phase,          GF
01/01/2014,         CHINA,          0
01/01/2014,         INDIA,          0.7
01/01/2014,         JAPAN,          0.5
02/01/2014,         CHINA,          0.2
02/01/2014,         INDIA,          0.5
02/01/2014,         JAPAN,          0.3
03/01/2014,         CHINA,          0.8
03/01/2014,         INDIA,          0.4
03/01/2014,         JAPAN,          1.0];

[Final]:
LOAD
  *,
  RangeSum([GF], Peek('GF_Accumulated')) AS [GF_Accumulated],
  IF([Phase] = Previous([Phase]), RangeSum([GF], Peek('GF_Accumulated2')),[GF]) AS [GF_Accumulated2]
Resident [Data] Order By [Phase],[DATE] ASC;


DROP Table [Data];

Regards,

Sokkorn

jagan · ‎2014-10-28

[Data]:

LOAD * Inline [

DATE, Phase, GF

01/01/2014, CHINA, 0

01/01/2014, INDIA, 0.7

01/01/2014, JAPAN, 0.5

02/01/2014, CHINA, 0.2

02/01/2014, INDIA, 0.5

02/01/2014, JAPAN, 0.3

03/01/2014, CHINA, 0.8

03/01/2014, INDIA, 0.4

03/01/2014, JAPAN, 1.0];

[Final]:

LOAD

*,

IF([Phase] = Peek([Phase]), RangeSum([GF], Peek('GF_Accumulated')), GF) AS [GF_Accumulated]

Resident [Data]

Order By [Phase], [DATE];

DROP Table [Data];

Hope this helps you.

Regards,

Jagan.

Kushal_Chawda · ‎2014-10-28

Please see the attached

its_anandrjs · ‎2014-10-28

Check this example there is another way for doing the same

[Data]:

LOAD Date#(DATE,'DD/MM/YYYY') AS DATE, Phase, GF;

LOAD * Inline [

DATE, Phase, GF

01/01/2014, CHINA, 0

01/01/2014, INDIA, 0.7

01/01/2014, JAPAN, 0.5

02/01/2014, CHINA, 0.2

02/01/2014, INDIA, 0.5

02/01/2014, JAPAN, 0.3

03/01/2014, CHINA, 0.8

03/01/2014, INDIA, 0.4

03/01/2014, JAPAN, 1.0];

[Final]:

LOAD

*,

if([Phase] = Previous([Phase]), [GF] + Peek('AccumulatedItem'), [GF]) as AccumulatedItem

Resident [Data] Order By [Phase],[DATE] ASC;

DROP Table [Data];

Regards

Anand

Report Inappropriate Content · ‎2014-10-28

Thanks Kush

Report Inappropriate Content · ‎2014-10-28

Your answer is correct .. thanks... but i need one more help

suppose

Date Phase GF

01/01/2014 CHINA 0

01/01/2014 INDIA 0.7

01/01/2014 JAPAN 0.5

02/01/2014 CHINA 0.2

02/01/2014 INDIA 0.5

02/01/2014 JAPAN 0.7

03/01/2014 CHINA 0.8

03/01/2014 INDIA 0.4

03/01/2014 JAPAN 1.0

01/02/2014 CHINA 0.2

01/02/2014 INDIA 0

01/02/2014 JAPAN 0.5

02/02/2014 CHINA 0.6

02/02/2014 INDIA 0.2

02/02/2014 JAPAN 0.1

03/02/2014 CHINA 0.4

03/02/2014 INDIA 0.6

03/02/2014 JAPAN 0.1

THE REQUIREMENT IS TO GET GF ACCUMULATED PHASE WISE and MONTH WISE

Date Phase GF GFACCUMILATED

01/01/2014 CHINA 0 0

02/01/2014 CHINA 0.2 0.2

03/01/2014 CHINA 0.8 0.8

01/02/2014 CHINA 0.2 0.2

02/02/2014 CHINA 0.6 0.8

03/02/2014 CHINA 0.4 1.2

01/01/2014 INDIA 0.7 0.7

02/01/2014 INDIA 0.5 1.2

03/01/2014 INDIA 0.4 1.6

01/02/2014 INDIA 0 0

02/02/2014 INDIA 0.2 0.2

03/02/2014 INDIA 0.6 0.8

01/01/2014 JAPAN 0.5 0.5

02/01/2014 JAPAN 0.3 0.8

03/01/2014 JAPAN 1.0 1.8

01/02/2014 JAPAN 0.7 0.7

02/02/2014 JAPAN 0.1 0.8

03/02/2014 JAPAN 0.1 0.9

Please help