Count in Qlikview script

[djaykumar]

Hi All,

I am severely facing issue on getting count in qlikview script

Please find data below

SNO         Material          SubMaterial          Serial            Time                 

 1              Plastic              Refill                        101867          8       

2               Plastic              Refill                        101867          3   

3               Plastic             Refill                         187650          4           

4               Rubber            Thick                        162907          5                 

Required out put

SNO         Material          SubMaterial          Serial            Time         Count        

 1              Plastic              Refill                        101867          8                 2

2               Plastic              Refill                        101867          3                 2

3               Plastic             Refill                         187650          4                  2

4               Rubber            Thick                        162907          5                 1

 Here in the above Material, Submaterial,Serial  are same and time should **bleep** once in the SNO 1 and 2

and in SNO 3   again  Material,Submaterial are same and serial is different then it should count 1

Ie., if Material,SubMaterial are same it should count  only Serial.

Please help me in this as it is urgent.

Thanks & Regards,

Jay

[sunny_talwar]

How are you getting a count of 2 for row number 3? Do you mean to get 1?

[djaykumar]

Hi Sunny,

No, count for row number 3 is 2 only for both row number 1 and 2 count will take 1 + row number 3 count will take 1 

because it should count unique Serial.

Please help me in this

Thanks & Regards,

Jay

[sunny_talwar · Accepted solution]

Got it, try this

Table:
LOAD * INLINE [
    SNO, Material, SubMaterial, Serial, Time
    1, Plastic, Refill, 101867, 8
    2, Plastic, Refill, 101867, 3
    3, Plastic, Refill, 187650, 4
    4, Rubber, Thick, 162907, 5
];

Left Join (Table)
LOAD Material,
	 SubMaterial,
	 Count(DISTINCT Serial) as Count
Resident Table
Group By Material, SubMaterial;

[djaykumar]

Hi Sunny,

Its working

Thanks,

Jay