Creation of a row / record

[Anonymous]

Dear Qv Experts,

I have the following table:

Date	ID	Ratings	RatingCount	TotalRatingCount	%RatingCount	Descp
01/05/2016	99999	9	2	4	50	Promoter
01/05/2016	99999	10	2	4	50	Promoter
01/05/2016	10000	1	4	9	44	Demoter
01/05/2016	10000	6	5	9	56	Demoter

Field Descp is described on basis of the following:

Rating	Descp
0 -- 6	Demoter
7 -- 8	Passive
9 -- 10	Promoter

I want to create records for ID 99999 and 10000. The mandate is to have a Promoter and a Demoter. If Promoter/ Demoter does not exist then Same Date, ID should be created with Descp As Demoter/ Promoter respt and rest values as Zero (0).

End result of Output should be:

Date	ID	Ratings	RatingCount	TotalRatingCount	%RatingCount	Descp
01/05/2016	99999	9	2	4	50	Promoter
01/05/2016	99999	10	2	4	50	Promoter
01/05/2016	10000	1	4	9	44	Demoter
01/05/2016	10000	6	5	9	56	Demoter
01/05/2016	99999	0	0	0	-	Demoter
01/05/2016	10000	0	0	0	-	Promoter

Appreciate your responses and answer. Thank You

[sunny_talwar]

Not entirely sure as to what the expected output needs to look like. Can you share more details?

[Kushal_Chawda]

how you are getting %Rating count without any other details?

[sunny_talwar]

Jadoo

[marcus_malinow]

Maybe something like this. Code generated in Qlik Sense, so slight syntax change on the first load.

Ratings:
LOAD
    [Date],
[ID],
[Ratings],
[RatingCount],
[TotalRatingCount],
[%RatingCount],
[Descp],
   
    [ID] & '_' & [Descp] as [ID_Descp_Check]
FROM [lib://220184]
(html, codepage is 1252, embedded labels, table is @1);

CONCATENATE (Ratings)
LOAD
    Max([Date]) as [Date],
[ID],
0 as [Ratings],
0 as [RatingCount],
0 as [TotalRatingCount],
//Null as [%RatingCount],
'Promoter' as [Descp]
RESIDENT Ratings
WHERE Not Exists ([ID_Descp_Check], [ID] & '_Promoter')
GROUP BY [ID], [Descp];

CONCATENATE (Ratings)
LOAD
    Max([Date]) as [Date],
[ID],
0 as [Ratings],
0 as [RatingCount],
0 as [TotalRatingCount],
//Null as [%RatingCount],
'Demoter' as [Descp]
RESIDENT Ratings
WHERE Not Exists ([ID_Descp_Check], [ID] & '_Demoter')
GROUP BY [ID], [Descp];

[Digvijay_Singh]

Try this out -

T1:

Load * inline [

Date ID Ratings RatingCount TotalRatingCount %RatingCount Descp

01/05/2016 99999 9 2 4                       50 Promoter

01/05/2016 99999 10 2 4                       50 Promoter

01/05/2016 10000 1 4 9                       44 Demoter

01/05/2016 10000 6 5 9                       56 Demote ](delimiter is spaces);

T2:

Load Date,ID, Concat(Descp,',') as DescpConcat

Resident T1

Group By Date,ID;

Concatenate(T1)

Load Date, ID, 0 as Ratings, 0 as RatingCount, 0 as TotalRatingCount, '-' as %RatingCount,

  If(wildmatch(DescpConcat,'*Promoter*'),'Demoter',If(WildMatch(DescpConcat,'*Demoter*'),'Promoter')) as Descp

Resident T2;

Drop table T2;