topic Re: Convert Measure into Dimension in QlikView

Convert Measure into Dimension

Thu, 15 Jun 2017 08:51:09 GMT

Hi ,

How to convert measures into dimension in a table .

For example:

Table 1:

LOAD

Division,

[Company Code],

Customer,

Due_Amount,

Over_Due_Amount,

[1-30],

[31-60],

[61-90],

[>90],

Total_AR

FROM TestData.qvd

Expected Output :

This should be converted as

Table 2:

LOAD

Segment,

Division,

[Company Code],

Customer,

Due_Bucket,

Cost

FROM Test1.qvd

Due_Bucket dimension will contain these values listed below as dimension and Cost measure would contain the value associated with each of these .

Due_Amount,

Over_Due_Amount,

[1-30],

[31-60],

[61-90],

[>90],

Total_AR

Thanks,

Prithvi

Re: Convert Measure into Dimension

kaushiknsolanki — Thu, 15 Jun 2017 08:58:49 GMT

Try the crosstable functionality.

crosstable(Due_Bucket,Cost,3)

LOAD

Division,

[Company Code],

Customer,

Due_Amount,

Over_Due_Amount,

[1-30],

[31-60],

[61-90],

[>90],

Total_AR

FROM TestData.qvd(qvd);

Regards,

Kaushik Solanki

Re: Convert Measure into Dimension

Thu, 15 Jun 2017 09:22:54 GMT

Example :

Table 1:

Division, [Company Code],Customer, Due_Amount, Over_Due_Amount, [1-30],[31-60], [61-90],[>90],Total_AR

A ,ABC,JACOB,100,200,50,20,70,50,300

X ,XYZ,BUNNY,300,100,40,100,100,50,400

expected output

Division, [Company Code],Customer, Due_Bucket,Cost

A,ABC,JACOB,Due_Amount,100

A,ABC,JACOB,Over_Due_Amount,200

A,ABC,JACOB,[1-30],50

A,ABC,JACOB,[31-60],20

A,ABC,JACOB,[61-90],70

A,ABC,JACOB,[>90],50

A,ABC,JACOB,Total_AR,300

X ,XYZ,BUNNY,Due_Amount,300

X ,XYZ,BUNNY,Over_Due_Amount,100

X ,XYZ,BUNNY,[1-30],40

X ,XYZ,BUNNY,[31-60],100

X ,XYZ,BUNNY,[61-90],100

X ,XYZ,BUNNY,[>90],50

X ,XYZ,BUNNY,Total_AR,400

Re: Convert Measure into Dimension

kaushiknsolanki — Thu, 15 Jun 2017 09:25:26 GMT

Yes, the code I gave you will give you same result.

Regards,

Kaushik Solanki

Re: Convert Measure into Dimension

Thu, 15 Jun 2017 09:51:07 GMT

Hi ,

Thanks for response .

But I'm getting field not found division.

Is syntax correct ?

[temp]:

crosstable(Due_Bucket,Cost,3)

LOAD

Division,

[Company Code],

Customer,

Due_Amount,

Over_Due_Amount,

[1-30],

[31-60],

[61-90],

[>90],

Total_AR

FROM AR_Fact.QVD;

Re: Convert Measure into Dimension

kaushiknsolanki — Thu, 15 Jun 2017 09:53:44 GMT

Do you have a field Division in source?

Also is the name of the field same?

Qlik is case sensitive.

Regards,

Kaushik Solanki