Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
djaykumar
Contributor
Contributor

Count in Qlikview script

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

 

Labels (1)
1 Solution

Accepted Solutions
sunny_talwar

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;

image.png

 

View solution in original post

4 Replies
sunny_talwar

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

djaykumar
Contributor
Contributor
Author

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

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;

image.png

 

djaykumar
Contributor
Contributor
Author

Hi Sunny,

Its working

Thanks,

Jay