Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
haymarketpaul
Contributor III

Generic Load Maybe?

I have the folliowing data where each row has a type of 1

Pot1

IDSomeTextType
1text11
2text21
3text31

I have another pot of data that looks like this (has many types other than 1)

Pot2

IDSomeText2Type
1text2
3text5
5text5
1text4

I need to consolidate the two so that i have one row for each ID and also maintain (in an outer joined table maybe?) which types they have

So from the data above i would need to know

IDType
11
12
14
21
31
35
55

so that a user would be able to filter the records based on one or more Types they select

Is there a good way to handle this in the load script somehow

Tags (1)
5 Replies
MVP
MVP

Re: Generic Load Maybe?

This should give you the last table:

LOAD ID, Type

FROM Pot1Table;

LOAD ID, Type

FROM Pot2Table;

I haven't fully understood how you want to consolidate the other fields into one record per ID.

haymarketpaul
Contributor III

Re: Generic Load Maybe?

Sorry wasn't clear i think...

I seem to be ending up with something like this

eg with ID # 1

IDSomeTextSomeText2Type
1text21
1text2
1text4

i would like it to be like this

IDSomeTextSomeText2
1text2text

And then either another column with the types listed like so 1,2,4 or better still perhaps if the are in a separate table so the user can select certain types and filter the data

krishna_2644
Valued Contributor III

Re: Generic Load Maybe?

Not sure if you are expecting something like below?:

1.PNG

Re: Generic Load Maybe?

Hi,

May be like this?

A:

LOAD ID,

     SomeText,

     SomeText2,

     Type

FROM

Book1.xlsx

(ooxml, embedded labels, table is Sheet1);


New:

LOAD Distinct ID,SomeText

Resident A Where Not IsNull(SomeText);

Left Join(New)

LOAD Distinct ID,SomeText2

Resident A Where Not IsNull(SomeText2);

DROP Table A;

MVP
MVP

Re: Generic Load Maybe?

Concerning the ID/SomeText/SomeText2 table, you can try joining Pot1 and Pot2 as shown above.

Alternatively you can do it like

TMP:

LOAD ID, SomeText

FROM Pot1Table;

CONCATENATE (TMP)

LOAD ID, SomeText2

FROM Pot2Table;

CONSOLIDATED:

LOAD ID,

          Only(SomeText) as SomeText,

          Only(SomeText2) as SomeText2

RESIDENT TMP

GROUP BY ID;

DROP TABLE TMP;

This would imply that you only have a unique SomeText resp. SomeText2 value per ID.

If this is not the case, but you still want a single records per ID, you can use concat:

CONSOLIDATED:

LOAD ID,

          Concat(SomeText,', ') as SomeText,

          Concat(SomeText2,', ') as SomeText2

RESIDENT TMP

GROUP BY ID;